﻿Imports System.Text
Imports System.Diagnostics.FileVersionInfo
Imports System.IO
Imports Global_Visas_Business_Layer.TasksEvents
Imports System.Windows
Imports System.Net.Mail


Public Class CommonDataFunctions
    Public Enum AdvertLinkType
        Person = 0
        Company = 1
    End Enum
    'Public Shared blnAmendDataBeforeEmail As Boolean
    Public Enum FlagType
        mbNone = 0
        mbInformation = vbInformation
        mbExclamation = vbExclamation
        mbQuestion = vbQuestion
        mbCritical = vbCritical
    End Enum
    Public Enum DefaultButton
        mbDefaultButton1 = vbDefaultButton1
        mbDefaultButton2 = vbDefaultButton2
        mbDefaultButton3 = vbDefaultButton3
    End Enum
    Public Enum CaseTypes
        Candidate = 1
        Company = 2
    End Enum
    Public Enum AddressTypes
        GV = 1
        Client = 2
        System = 3
    End Enum
    Public Enum EmailType
        Blank = 0
        Candidate = 1
        Contact = 2
        Both = 3
    End Enum
    Public Structure EmailTemplateInfo
        Public TemplateName As String
        Public SubjectLine As String
        Public Body As String
    End Structure
    Public Structure AccountsSummary
        Public QuoteNet As Decimal
        Public QuoteGross As Decimal
        Public InvoicedNet As Decimal
        Public InvoicedGross As Decimal
        Public Payments As Decimal
        Public Balance As Decimal
    End Structure

    Public Structure DocumentsSummary
        Public Required As Integer
        Public Received As Integer
        Public InDocStore As Integer
    End Structure



    Public Structure EmailTemplate
        Public Subject As String
        Public Body As String
    End Structure

    Public Enum EventTypes
        NotSet = 0
        EventCase = 1
        EventPerson = 2
        EventCompany = 3
        EventUserTask = 4
    End Enum
    '    Public Shared blnCancelEmail As Boolean
    Public Shared EMAILBODYSTRING As String
    Public Shared EMAILSUBJECTSTRING As String
    Public Shared EMAILBCCSTRING As String
    Public Shared EMAILFROMSTRING As String
    Public Shared EMAILTOSTRING As String




    Private Declare Function GetWindowLong Lib "user32" _
   Alias "GetWindowLongA" (ByVal hWnd As Int32, _
   ByVal nIndex As Int32) As Long

    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    'Private Declare Function SetWindowsHookEx Lib "user32" _
    'Alias "SetWindowsHookExA" (ByVal idHook As Long, _
    'ByVal lpfn As Long, ByVal hmod As Long, _
    'ByVal dwThreadId As Long) As Long

    '    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" _
    '(ByVal idHook As Int16, ByVal lpfn As WinHookDelegate, ByVal hmod As Int64, _
    'ByVal dwThreadId As Int64) As Int64


    '    Private Declare Function GetWindowPlacement Lib "user32" _
    '    (ByVal hWnd As Long, ByVal lpwndp1 As WINDOWPLACEMENT) As Long
    '    Private Declare Function SetWindowText Lib "user32" _
    '    Alias "SetWindowTextA" (ByVal hWnd As Long, _
    '    ByVal lpString As String) As Long
    Private Declare Function SetDlgItemText Lib "user32" _
    Alias "SetDlgItemTextA" (ByVal hDlg As Long, _
    ByVal nIDDlgItem As Long, ByVal lpString As String) _
    As Long
    Private Declare Function MessageBox Lib "user32" _
    Alias "MessageBoxA" (ByVal hWnd As Long, _
    ByVal lpText As String, ByVal lpCaption As String, _
    ByVal wType As Long) As Long
    Private Declare Function MoveWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal X As Long, _
    ByVal Y As Long, ByVal nWidth As Long, _
    ByVal nHeight As Long, ByVal bRepaint As Long) _
    As Long
    Private Declare Function FindWindow Lib "user32" _
        Alias "FindWindowA" (ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
    Private Declare Function UnhookWindowsHookEx Lib "user32" _
    (ByVal hHook As Long) As Long

    Private Const GWL_HINSTANCE = (-6)
    Private Const WH_CBT = 5
    Private Const HCBT_ACTIVATE = 5


    Private Shared strYes As String
    Private Shared strNo As String
    Private Shared strCancel As String
    Private Shared strPrompt As String
    Private Shared StrTitle As String

    'Private Shared MSGHOOK As MSGBOX_HOOK_PARAMS
    Private Shared mLeft As Long 'The MsgBox's Left position
    Private Shared mTop As Long  'The MsgBox's Top position
    Private Shared sIcon As String
    'Control ID for the message prompt
    Private Const IDPROMPT = &HFFFF&
    Public Structure ConsultantInfo
        Public ConId As Integer
        Public ConsultantName As String
        Public DDI As String
        Public DDIUK As String
        Public Fax As String
        Public JobTitle As String
        Public Accreditation As String
        Public Dislaimer As Integer
        Public Email As String
        Public DepartmentId As Integer
        Public SupervisorId As Integer
        Public OISCId As Integer
        Public DisclaimerId As Integer
        Public StatusId As Integer
        Public NTUser As String
        Public BranchID As Integer
        Public AccessLevel As Integer
        Public EmailDraftLoc As String
    End Structure
    Public Enum DBIdTypes
        CompanyID = 1
        PersonID = 2
        CaseID = 3
        InvoiceID = 4
    End Enum
    Public Enum ViewByStatus
        AllOpen = 1
        PreSale = 2
        Initiated = 3
        Closed = 4
        AllCases = 5
    End Enum
    'Public Shared blnCancelPaymentPlan As Boolean
    Public Shared Function DeleteHistory(ByVal conid As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            If db.ExecuteSQL("Delete from S_Userhistory where Userhistory_Userid = " & conid) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally

            db = Nothing
        End Try
    End Function
    Public Shared Function HistoryData(ByVal Conid As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("Select Userhistory_LinkType, Userhistory_DT, Case Userhistory_LinkType when 1 then 'Company' when 2 then 'Person' ")
                .Append("when 3 then 'Case' when 4 then 'Invoice' end as Type, Userhistory_LinkID, Userhistory_text FROM S_Userhistory ")
                .Append("WHERE Userhistory_UserId = " & Conid)
                .Append("ORDER By Userhistory_dt desc")

            End With
            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function GetBranchSMSLink(ByVal caseid As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append(" Select dbo.L_Branch.Branch_SMSLink FROM         dbo.D_Case INNER JOIN")
                .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
                .Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID")
                .Append(" WHERE dbo.D_Case.Case_ID = " & caseid)
            End With
            Dim strSMSLink As Object = db.GetSQLScalar(SQL.ToString)
            If Not strSMSLink Is Nothing Then
                If strSMSLink.ToString.Trim <> String.Empty Then
                    Return strSMSLink.ToString.Trim
                Else
                    Return String.Empty
                End If
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function CaseListSQL(ByVal CaseView As ViewByStatus, ByVal CaseType As CaseTypes) As String

        Dim SQL As New StringBuilder

        With SQL
            .Append("SELECT D_Case.Case_ID as CaseID, Case Case_ActiveStatus when 1 then 'ON HOLD: ' else '' end + L_CaseStages.CaseStage_Title as Status,")
            '.Append("SELECT D_Case.Case_ID, Case Case_ActiveStatus when 1 then 'ON HOLD: ' else '' end + L_CaseStages.CaseStage_Title as CaseStage,")
            .Append("L_Products.Product_Name as Product, L_Consultant_Sales.con_name AS Consultant, L_Consultant_Case.con_name AS Caseworker, L_Consultant_Coordinator.con_name as Coordinator, ")
            .Append(" dbo.V_CaseWithPeople.Candidate, ")
            '.Append("L_Products.Product_Name, L_Consultant_Sales.con_name AS Consultant, L_Consultant_Case.con_name AS Caseworker,")
            .Append("V_CaseWithPeople.Contact as Contact , CONVERT(VARCHAR(10),")
            .Append("D_Case.Case_DTStatus, 103) AS DateStatus FROM V_CaseWithPeople RIGHT OUTER JOIN D_Case ON V_CaseWithPeople.Case_ID = D_Case.Case_ID ")
            .Append("LEFT OUTER JOIN L_Consultant L_Consultant_Case ON D_Case.Case_ConCWID = L_Consultant_Case.Con_ID LEFT OUTER JOIN L_Consultant L_Consultant_Sales ON ")
            .Append("D_Case.Case_ConSaleID = L_Consultant_Sales.Con_ID LEFT OUTER JOIN L_CaseStages ON D_Case.Case_StatusID = L_CaseStages.CaseStage_Number LEFT OUTER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID ")
            .Append("Left outer join L_Consultant L_Consultant_Coordinator on D_Case.Case_CoordinatorID = L_Consultant_Coordinator.Con_Id ")
            '.Append("Left join V_CaseContact on D_Case.Case_ID = V_CaseContact.Case_ID ")
            Select Case CaseView
                Case ViewByStatus.AllOpen
                    .Append("WHERE (D_Case.Case_StatusID < 1000) And ")
                Case ViewByStatus.AllCases
                    .Append("WHERE ")
                Case ViewByStatus.Closed
                    .Append("WHERE (D_Case.Case_StatusID >= 1000) And ")
                Case ViewByStatus.PreSale
                    .Append("WHERE (D_Case.Case_StatusID < 100) And ")
                Case ViewByStatus.Initiated
                    .Append("WHERE (D_Case.Case_StatusID between 100 and 999) And ")

            End Select
            Select Case CaseType
                Case CaseTypes.Candidate
                    .Append("(D_Case.Case_CandidateID = " & GlobalCRM.CurrentCandidateID & ") ")
                Case CaseTypes.Company
                    .Append("(D_Case.Case_companyID = " & GlobalCRM.CurrentCompanyID & ") ")

            End Select

            .Append("Order By D_Case.Case_ID Desc")
            Return .ToString
        End With

    End Function
    Public Shared Function SubsidiaryList(ByVal CompanyID As Long) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("select company_id,company_name from d_company where company_parentid = " & CompanyID)
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function




    Public Shared Function GetControlGroups(ByVal ControlGroup As Integer) As DataTable
        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        SQL.Append("Select ControlGroup_Table, ControlGroup_TableName, ControlGroup_Name, ControlGroup_IDName, ControlGroup_Formname, ControlGroup_Usage from I_ControlGroups where ControlGroup_ID = " & ControlGroup)
        Return DB.GetSQLDataTable(SQL.ToString)

    End Function

    Public Shared Function GetLoggedOnConsultantID() As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim singleObject As Singleton = Singleton.Instance
        Return (DB.GetSQLScalar("select con_id from l_consultant where Con_Name = '" & singleObject.CurrentConName & "'"))
        DB = Nothing
    End Function
    Public Shared Function GetBranchID(ByVal Conid As Integer) As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLScalar("select con_branchid from l_consultant where con_id =  '" & Conid & "'"))
        DB = Nothing
    End Function
    Public Shared Function GetBranchIDFromCase(ByVal caseid As Integer) As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder
            With sql
                .Append(" Select dbo.L_ProductTypes.ProductType_BranchID")
                .Append(" FROM  dbo.L_ProductTypes INNER JOIN")
                .Append(" dbo.L_Products ON dbo.L_ProductTypes.ProductType_ID = dbo.L_Products.Product_Type INNER JOIN")
                .Append(" dbo.D_Case ON dbo.L_Products.Product_ID = dbo.D_Case.Case_ProductID")
                .Append(" WHERE     (dbo.D_Case.Case_ID = " & caseid & ")")
            End With
            Return DB.GetSQLScalar(sql.ToString)
        Catch ex As Exception
            Return (0)
        Finally
            sql = Nothing
            DB = Nothing
        End Try
    End Function
    Public Shared Function GetLoggedOnConsultantDeptID() As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLScalar("select con_Department from l_consultant where con_ntusername =  '" & GlobalCRM.CurrentNTUserName & "'"))
        DB = Nothing
    End Function

    Public Shared Function GetOption(ByVal strOption) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return DB.GetSQLScalar("Select Option_Value from L_Options where Option_Text='" & strOption & "'")
        DB = Nothing
    End Function

    Public Shared Function GetLoggedOnConsultantAsList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Return (DB.GetSQLDataTable("select con_name, con_id from l_consultant where con_ntusername = '" & GlobalCRM.CurrentNTUserName & "'"))
    End Function

    Public Shared Function CreditControllerList(Optional ByVal ActiveOnly As Boolean = True, Optional ByVal DepartmentId As Integer = 0, Optional ByVal ConsultantID As Integer = -1, Optional ByVal intBranch As Integer = -1) As DataTable
        ' get list of Consultants

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        With SQL
            .Append("select con_id, con_name, con_status from l_consultant where con_iscreditcontroller >= 1")
            Return DB.GetSQLDataTable(.ToString)
        End With

    End Function
    Public Shared Function GetSQLDatatable(ByVal sql As String) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLDataTable(sql)
        Catch ex As Exception
            Return Nothing
        Finally

            db = Nothing
        End Try
    End Function
    Public Shared Function ExecuteSQL(ByVal sql As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            If db.ExecuteSQL(sql) = True Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function ExecuteSQL(ByVal SQL As String, ByVal blnGetIdentity As Boolean) As Long
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim returnvariable As Long
        Try
            returnvariable = db.ExecuteSQL(SQL, True)
            If returnvariable > 0 Then
                Return returnvariable
            Else
                Return 0
            End If
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function DBIdExists(ByVal DBId As Long, ByVal DBIDType As DBIdTypes) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            Dim c As Integer

            Select Case DBIDType
                Case DBIdTypes.CaseID
                    SQL.Append("select count(*) from d_case where case_id = " & DBId)
                Case DBIdTypes.CompanyID
                    SQL.Append("select count(*) from d_company where company_id = " & DBId)
                Case DBIdTypes.PersonID
                    SQL.Append("select count(*) from d_person where person_id = " & DBId)
                Case DBIdTypes.InvoiceID
                    SQL.Append("select count(*) from a_invoices where invoice_id = " & DBId)

            End Select

            c = db.GetSQLScalar(SQL.ToString)

            If c > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function IDSearch(ByVal lngSearchID As Long, ByVal lngSearchType As Integer) As String
        ' perform ID search on company,person,case or invoice.
        Try


            IDSearch = String.Empty

            ' handle Case search
            If lngSearchType = GlobalCRM.enuSearchType.eCase Then

                ' check Case_ID exists
                If DBIdExists(lngSearchID, DBIdTypes.CaseID) = False Then
                    Return "Invalid Case No."

                End If

                GlobalCRM.CurrentCaseID = lngSearchID

                GblFunc_Goto_ID(GlobalCRM.CurrentCaseID, DBIdTypes.CaseID)
                IDSearch = "ok"

                ' handle Company search
            ElseIf lngSearchType = GlobalCRM.enuSearchType.eCompany Then

                ' check Company_ID exists
                If DBIdExists(lngSearchID, DBIdTypes.CompanyID) = False Then
                    Return "Invalid Company No."

                End If

                GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.eCompany
                GlobalCRM.CurrentCaseID = 0
                GlobalCRM.CurrentCompanyID = lngSearchID
                GblFunc_Goto_ID(GlobalCRM.CurrentCompanyID, DBIdTypes.CompanyID)
                IDSearch = "ok"

                ' handle Person search
            ElseIf lngSearchType = GlobalCRM.enuSearchType.ePerson Then

                ' check Person_ID exists
                If DBIdExists(lngSearchID, DBIdTypes.PersonID) = False Then
                    Return "Invalid Person No."

                End If

                GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.ePerson
                GlobalCRM.CurrentCaseID = 0
                GlobalCRM.CurrentCandidateID = lngSearchID
                GblFunc_Goto_ID(GlobalCRM.CurrentCandidateID, DBIdTypes.PersonID)
                IDSearch = "ok"

                ' handle Invoice search
            ElseIf lngSearchType = GlobalCRM.enuSearchType.eInvoice Then

                ' check Invoice_ID exists
                If DBIdExists(lngSearchID, DBIdTypes.InvoiceID) = False Then
                    Return "Invalid Invoice No."

                End If

                GlobalCRM.CurrentCaseID = 0
                GlobalCRM.CurrentInvoiceId = lngSearchID
                GlobalCRM.CurrentCaseID = Cases.GetCaseIdFromInvoiceID(GlobalCRM.CurrentInvoiceId)
                GblFunc_Goto_ID(GlobalCRM.CurrentInvoiceId, DBIdTypes.InvoiceID)
                IDSearch = "ok"
            Else
                Return String.Empty
            End If

        Catch ex As Exception
            Return String.Empty
        Finally

        End Try
    End Function
    Public Shared Function GetSMSTemplates(ByVal branchid As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLDataTable("Select SMSTemplate_id,SMSTemplate_Name,SMSTemplate_Body from d_SMSTemplate where SMSTemplate_BranchID = " & branchid)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GblFunc_Goto_ID(ByVal DataID As Long, ByVal DataType As DBIdTypes) As Integer
        ' used immediately prior to opening the frmMainDisplay form, to log user movement around CRM
        ' by logging an event in the D_Event table and logging a history in the S_UserHistory table
        Dim tsk As New TasksEvents
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try


            Dim EventMessage As New StringBuilder
            Dim HistoryMessage As New StringBuilder

            'Check to see if provided record ID exists in the relevent table.
            Select Case DataType

                Case DBIdTypes.CompanyID

                    EventMessage.Append("Opened company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
                    HistoryMessage.Append(GlobalCRM.CurrentCompanyName)

                    GlobalCRM.TabSelection = GlobalCRM.enumSelectTab.eCompany
                Case DBIdTypes.PersonID

                    EventMessage.Append("Opened Person " & GlobalCRM.CurrentCandidateName & " (" & GlobalCRM.CurrentCandidateID & ")")
                    HistoryMessage.Append(GlobalCRM.CurrentCandidateName)

                    If GlobalCRM.CurrentCompanyID <> 0 Then
                        EventMessage.Append(" of company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
                        HistoryMessage.Append(" (" & GlobalCRM.CurrentCompanyName & ")")
                    Else
                        EventMessage.Append(", private individual")
                    End If
                    GlobalCRM.TabSelection = GlobalCRM.enumSelectTab.ePerson

                Case DBIdTypes.CaseID

                    Dim SQL As New StringBuilder

                    EventMessage.Append("Opened case number " & GlobalCRM.CurrentCaseID)

                    With SQL
                        .Append("SELECT ISNULL(L_Products.Product_Name, 'Unspecified') AS Product")
                        .Append(" FROM D_Case LEFT OUTER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID")
                        .Append(" WHERE D_Case.Case_ID = " & GlobalCRM.CurrentCaseID)
                    End With


                    HistoryMessage.Append(db.GetSQLScalar(SQL.ToString) & " for " & GlobalCRM.CurrentCandidateName)

                    If GlobalCRM.CurrentCompanyID <> 0 Then
                        EventMessage.Append(" for company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
                        HistoryMessage.Append("(" & GlobalCRM.CurrentCompanyName & ")")
                    Else
                        EventMessage.Append(" for private individual" & GlobalCRM.CurrentCandidateName & " (" & GlobalCRM.CurrentCandidateID & ")")
                    End If

                    GlobalCRM.TabSelection = GlobalCRM.enumSelectTab.eCase
                Case DBIdTypes.InvoiceID

                    EventMessage.Append("Opened invoice number " & GlobalCRM.CurrentInvoiceId & " for case number " & GlobalCRM.CurrentCaseID)
                    HistoryMessage.Append("For Case number " & GlobalCRM.CurrentCaseID)

                    If GlobalCRM.CurrentCompanyID <> 0 Then
                        EventMessage.Append(" for company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
                        HistoryMessage.Append(GlobalCRM.CurrentCompanyName)
                    Else
                        EventMessage.Append(" for private individual" & GlobalCRM.CurrentCandidateName & " (" & GlobalCRM.CurrentCandidateID & ")")
                        HistoryMessage.Append(" (" & GlobalCRM.CurrentCandidateName & ")")
                    End If

            End Select

            ' log event
            tsk.GblFunc_Add_SysEvent("Activity", EventMessage.ToString)
            ' log history
            Gblfunc_Add_History(DataID, DataType, HistoryMessage.ToString)
        Catch ex As Exception
        Finally
            tsk = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function Gblfunc_Add_History(ByVal LinkID As Long, ByVal LinkType As Integer, ByVal Message As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim cmn As New CommonFuncs
        Try


            Dim SQL As New StringBuilder

            If LinkID = 0 Or LinkType = 0 Or Message = String.Empty Then
                Exit Function
            End If

            If Message.Length > 100 Then
                Message = Left(Message, 100)
            End If

            With SQL
                .Append("Insert into S_UserHistory")
                .Append(" (UserHistory_LinkType, UserHistory_LinkID, UserHistory_UserID, UserHistory_Text)")
                .Append(" VALUES (" & LinkType & ", " & LinkID & ", " & GlobalCRM.CurrentConID & ", '" & cmn.SwapQuotes(Message) & "')")
            End With

            If db.ExecuteSQL(SQL.ToString) Then

                Dim myData As DataTable

                'Check if more than 20 and remove excess

                SQL = New StringBuilder

                With SQL
                    .Append("Select UserHistory_ID FROM S_Userhistory ")
                    .Append("WHERE UserHistory_UserId = " & GlobalCRM.CurrentConID)
                    .Append(" Order By Userhistory_dt")
                End With

                myData = db.GetSQLDataTable(SQL.ToString)

                If Not IsNothing(myData) Then

                    If myData.Rows.Count > 20 Then

                        Dim OldestHistoryId As Long
                        Dim TopId As Long
                        Dim BottomId As Long
                        Dim myHistory As DataTable

                        OldestHistoryId = myData.Rows(20).Item("userhistory_id")

                        SQL = New StringBuilder

                        With SQL
                            .Append("select top 20 userhistory_id from s_userhistory  ")
                            .Append("where UserHistory_UserId = " & GlobalCRM.CurrentConID)
                            .Append(" and Userhistory_id < " & OldestHistoryId & " order by userhistory_id")
                        End With

                        myHistory = db.GetSQLDataTable(SQL.ToString)

                        TopId = myData.Rows(myData.Rows.Count - 21).Item("userhistory_id")
                        BottomId = myData.Rows(0).Item("userhistory_id")

                        myHistory.Dispose()

                        'Delete any history for this consultant with a lower history id
                        SQL = New StringBuilder

                        With SQL
                            .Append("delete from s_userhistory where UserHistory_UserId = " & GlobalCRM.CurrentConID)
                            .Append(" and Userhistory_id between " & BottomId & " and " & TopId)
                        End With

                        db.ExecuteSQL(SQL.ToString)

                    End If

                End If

            End If
        Catch ex As Exception
        Finally
            db = Nothing
            cmn = Nothing
        End Try
    End Function
    'Public Shared Function GetDBConnectionString() As String
    '    ' get database connection string from the web config file

    '    Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
    '    Dim strDatabase As String = CType(configurationAppSettings.GetValue("globalConnString", GetType(System.String)), String) & ";Database=global;User Id=GlobalWeb;Password=0cSn3FKzr9;timeout=30;"
    '    GetDBConnectionString = strDatabase

    'End Function
    Public Shared Function GetConsultantInfo(ByVal ConId As Integer) As ConsultantInfo

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder
        Try
            SQL = New StringBuilder
            Dim myData As DataTable

            GetConsultantInfo = Nothing

            With SQL
                .Append("Select con_name, Con_DDI, Con_DDIUK, Con_Fax, Con_Jobtitle, Con_Accreditation, Con_DisclaimerID,")
                .Append("con_email,con_department,con_supervisorid,con_oisc,con_disclaimerid,")
                .Append("con_status,con_ntusername, con_BranchID,Con_SecurityLevel ")
                .Append(" from L_Consultant where Con_ID = " & ConId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    Dim myCon As New ConsultantInfo

                    With myCon
                        .ConId = ConId
                        .ConsultantName = IIf(IsDBNull(myData.Rows(0).Item("con_name")), "", myData.Rows(0).Item("con_name"))
                        .DDI = IIf(IsDBNull(myData.Rows(0).Item("con_ddi")), "", myData.Rows(0).Item("con_ddi"))
                        .DDIUK = IIf(IsDBNull(myData.Rows(0).Item("con_ddiuk")), "", myData.Rows(0).Item("con_ddiuk"))
                        .Fax = IIf(IsDBNull(myData.Rows(0).Item("con_fax")), "", myData.Rows(0).Item("con_fax"))
                        .JobTitle = IIf(IsDBNull(myData.Rows(0).Item("con_jobtitle")), "", myData.Rows(0).Item("con_jobtitle"))
                        .Accreditation = IIf(IsDBNull(myData.Rows(0).Item("con_accreditation")), "", myData.Rows(0).Item("con_accreditation"))
                        .Dislaimer = myData.Rows(0).Item("con_disclaimerid")
                        .Email = IIf(IsDBNull(myData.Rows(0).Item("con_email")), "", myData.Rows(0).Item("con_email"))
                        .DepartmentId = myData.Rows(0).Item("con_department")
                        .SupervisorId = myData.Rows(0).Item("con_supervisorid")
                        .OISCId = myData.Rows(0).Item("con_oisc")
                        .DisclaimerId = myData.Rows(0).Item("con_disclaimerid")
                        .StatusId = myData.Rows(0).Item("con_status")
                        .NTUser = IIf(IsDBNull(myData.Rows(0).Item("con_ntusername")), "", myData.Rows(0).Item("con_ntusername"))
                        .BranchID = myData.Rows(0).Item("con_BranchID")
                        .AccessLevel = myData.Rows(0).Item("Con_SecurityLevel")
                    End With

                    Return myCon
                End If
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function ConsultantList(Optional ByVal ActiveOnly As Boolean = True, Optional ByVal DepartmentId As Integer = 0, Optional ByVal ConsultantID As Integer = -1, Optional ByVal intBranch As Integer = -1) As DataTable
        ' get list of Consultants

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("select con_id, con_name, con_status from l_consultant")
                If ActiveOnly Then
                    .Append(" where ( con_status = 1 ")

                    If intBranch > -1 Then
                        .Append(" and con_BranchID = " & CStr(intBranch))
                    End If

                    If DepartmentId > 0 Then
                        .Append(" and con_department = " & DepartmentId)
                    End If

                    .Append(" ) ")

                    If ConsultantID > 0 Then
                        .Append("or Con_id = " & CStr(ConsultantID))
                    End If
                End If
                .Append(" order by con_name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function ConsultantsList(Optional ByVal intBranch As Integer = -1) As DataTable
        ' get list of Consultants

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("select con_id, con_name, con_status from l_consultant")

                If intBranch > -1 Then
                    .Append(" and con_BranchID = " & CStr(intBranch))
                End If
                .Append(" order by con_name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function LogInUser(ByVal con_id As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim dt As DataTable
            SQL.Append("select * from D_LoggedInConsultants where con_id = " & con_id)
            dt = db.GetSQLDataTable(SQL.ToString)
            If dt Is Nothing Then
                'create new entry for that person in table
                SQL = New StringBuilder
                SQL.Append("insert into D_LoggedInConsultants(con_id,date_lastloggedin,version_using) values (" & con_id & ",getdate(),'" & My.Application.Info.Version.ToString & "')")
                If db.ExecuteSQL(SQL.ToString) = True Then
                    Return True
                End If
            End If
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    SQL = New StringBuilder
                    SQL.Append("update D_LoggedInConsultants set date_lastloggedin = getdate(),version_using = '" & My.Application.Info.Version.ToString & "' where con_id = " & con_id)
                    If db.ExecuteSQL(SQL.ToString) = True Then
                        Return True
                    End If
                Else
                    'create new entry for that person in table
                    SQL = New StringBuilder
                    SQL.Append("insert into D_LoggedInConsultants(con_id,date_lastloggedin,version_using) values (" & con_id & ",getdate(),'" & My.Application.Info.Version.ToString & "')")
                    If db.ExecuteSQL(SQL.ToString) = True Then
                        Return True
                    End If
                End If
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetCaseCandidateID(ByVal CaseID As Long) As Long

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            Return dataAccess.GetSQLScalar("select case_candidateid from d_case where case_id = " & CaseID)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try

    End Function
    Public Shared Function GetCandidateName(ByVal PersonId As Long) As String

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            Return dataAccess.GetSQLScalar("select person_fname + ' ' + person_lname as fullname from d_person where person_id = " & PersonId)
        Catch ex As Exception
            Return String.Empty
        Finally
            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function GetCaseContactId(ByVal CaseId As Long) As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            Return dataAccess.GetSQLScalar("select case_contactid from d_case where case_id = " & CaseId)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function GetCaseCompanyId(ByVal CaseId As Long) As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            Return dataAccess.GetSQLScalar("select case_companyid from d_case where case_id = " & CaseId)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function GetConsultantName(ByVal ConsultantId As Integer) As String
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            Return dataAccess.GetSQLScalar("select con_name from l_consultant where con_id = " & ConsultantId)

        Catch ex As Exception
            Return String.Empty
        Finally
            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function GetCandidateCompanyID(ByVal PersonID As Long) As Long
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            Return dataAccess.GetSQLScalar("select person_companyid from d_person where person_id = " & PersonID)
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try

    End Function
    Public Shared Function GetCompanyName(ByVal CompanyID As Long) As String
        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try
            If CompanyID > 0 Then
                Return dataAccess.GetSQLScalar("select Company_Name from d_company where company_id = " & CompanyID)
            Else
                Return 0
            End If
        Catch ex As Exception
            Return 0
        Finally
            dataAccess = Nothing
        End Try

    End Function
    Public Shared Function GetWhereHeard(ByVal LinkId As Long, ByVal LinkType As AdvertLinkType) As String

        Dim dataAccess As New Global_Visas_Data_Layer.DataAccess
        Try

            Return dataAccess.GetSQLScalar("select advertising_wherehear from d_advertising where advertising_linkid = " & LinkId & " and Advertising_LinkType = " & LinkType)
        Catch ex As Exception
            Return String.Empty
        Finally
            dataAccess = Nothing
        End Try
    End Function
    Public Shared Function GblFunc_Get_Dataset(ByVal CaseID As Long, ByVal IntSet As Integer) As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try


            ' IntSet 1 = Case Stages Diagram
            ' IntSet 2 = Multi Function Button Group
            ' IntSet 3 = Stock Letter Set
            ' IntSet 4 = Charge Type Group
            ' IntSet 5 = Event type group
            ' IntSet 6 = DT Category
            ' IntSet 7 = Calc Set


            Dim ProductID As Integer, ProductType As Integer
            Dim SQL As New StringBuilder

            SQL.Append("Select Case_ProductId from D_Case where case_Id = " & CaseID)
            ProductID = db.GetSQLScalar(SQL.ToString)

            If ProductID <> 0 Then

                'Get the data sets from the product
                Dim myTab As DataTable

                SQL = New StringBuilder

                With SQL
                    .Append("Select Product_Type, Product_StagediagramId, Product_MFBGroupID, Product_SLSID, Product_ChargeGroupID, Product_EventGroupID,")
                    .Append("Product_DTCategory, Product_CalcSet from L_Products where product_id = " & ProductID)
                End With

                myTab = db.GetSQLDataTable(SQL.ToString)

                If Not IsNothing(myTab) Then

                    If myTab.Rows.Count > 0 Then

                        Dim myRow As DataRow = myTab.Rows(0)

                        ProductType = myRow.Item("product_type")

                        'Set the function to the required value
                        Select Case IntSet
                            Case 1
                                GblFunc_Get_Dataset = myRow.Item("Product_StageDiagramID")

                            Case 2
                                GblFunc_Get_Dataset = myRow.Item("Product_MFBGroupID")

                            Case 3
                                GblFunc_Get_Dataset = myRow.Item("Product_SLSID")

                            Case 4
                                GblFunc_Get_Dataset = myRow.Item("Product_ChargeGroupID")

                            Case 5
                                GblFunc_Get_Dataset = myRow.Item("Product_EventGroupID")

                            Case 6
                                GblFunc_Get_Dataset = myRow.Item("Product_DTCategory")

                            Case 7
                                GblFunc_Get_Dataset = myRow.Item("Product_CalcSet")

                            Case Else
                                GblFunc_Get_Dataset = 0

                        End Select

                        myTab.Dispose()

                        'Do we have what we want?
                        If GblFunc_Get_Dataset = 0 Then
                            'No, try again with the product type

                            SQL = New StringBuilder

                            With SQL
                                .Append("Select ProductType_StageDiagramID, ProductType_MFBGroupID, ProductType_SLSID, ProductType_ChargeGroupID, ProductType_EventGroupID, ProductType_DTCategory, ProductType_CalcSet ")
                                .Append(" FROM L_ProductTypes ")
                                .Append(" WHERE producttype_id = " & ProductType)
                            End With

                            myTab = db.GetSQLDataTable(SQL.ToString)

                            If Not IsNothing(myTab) Then
                                If myTab.Rows.Count > 0 Then

                                    myRow = myTab.Rows(0)

                                    Select Case IntSet
                                        Case 1
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_StageDiagramID")

                                        Case 2
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_MFBGroupID")

                                        Case 3
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_SLSID")

                                        Case 4
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_ChargeGroupID")

                                        Case 5
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_EventGroupID")

                                        Case 6
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_DTCategory")

                                        Case 7
                                            GblFunc_Get_Dataset = myRow.Item("Producttype_CalcSet")

                                        Case Else
                                            GblFunc_Get_Dataset = 0

                                    End Select
                                End If

                            End If
                        End If


                    End If


                End If

            End If
        Catch ex As Exception
            Return 0
        Finally

        End Try
    End Function
    Public Shared Function GetCurrencyCodeCharacter(ByVal CurrentCaseID As Long, Optional ByVal rpt As Boolean = False)
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            If rpt Then
                With SQL
                    .Append("SELECT dbo.L_Country.Country_Symbol,dbo.L_Country.Country_CurrencyDesc")
                    .Append(" FROM dbo.D_Case INNER JOIN")
                    .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                    .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
                    .Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID INNER JOIN")
                    .Append(" dbo.L_Country ON dbo.L_Branch.Branch_CountryID = dbo.L_Country.Country_ID")
                    .Append(" where D_Case.Case_ID=" & CStr(CurrentCaseID))
                    Return db.GetSQLDataSet(.ToString)
                End With
            Else
                With SQL
                    .Append("SELECT dbo.L_Country.Country_Symbol")
                    .Append(" FROM dbo.D_Case INNER JOIN")
                    .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                    .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
                    .Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID INNER JOIN")
                    .Append(" dbo.L_Country ON dbo.L_Branch.Branch_CountryID = dbo.L_Country.Country_ID")
                    .Append(" where D_Case.Case_ID=" & CStr(CurrentCaseID))
                    Return db.GetSQLScalar(.ToString)
                End With
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function GetGovTax(ByVal CurrentCaseID As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("SELECT dbo.L_Country.Country_Tax")
                .Append(" FROM dbo.D_Case INNER JOIN")
                .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
                .Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID INNER JOIN")
                .Append(" dbo.L_Country ON dbo.L_Branch.Branch_CountryID = dbo.L_Country.Country_ID")
                .Append(" where D_Case.Case_ID=" & CStr(CurrentCaseID))
            End With
            Dim obj As Object = db.GetSQLScalar(SQL.ToString)
            If Not obj Is Nothing Then
                Return obj.ToString
            Else
                Return String.Empty
            End If
            ' Return GVData.ExecuteScalar(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetLoggedOnConsultantBranchID() As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim singleObject As Singleton = Singleton.Instance

        Try
            'Return (db.GetSQLScalar("select con_branchid from l_consultant left join l_Departments on L_Consultant.COn_Department = L_Departments.Department_BranchID where con_ntusername =  '" & GlobalCRM.CurrentNTUserName & "'"))
            Return (db.GetSQLScalar("select con_branchid from l_consultant left join l_Departments on L_Consultant.COn_Department = L_Departments.Department_BranchID where con_name =  '" & singleObject.CurrentConName & "'"))
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function GetDepartmentName() As String
        ' get Department name for current user
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Dim singleObject As Singleton = Singleton.Instance
        Try

            SQL = New StringBuilder
            With SQL
                .Append("select department_name from l_departments d, l_consultant c ")
                .Append("where c.con_department = d.department_id ")

                .Append("and con_ntusername = '" & singleObject.CurrentConName & "'")

                Return db.GetSQLScalar(.ToString)
            End With
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
            SQL = Nothing
        End Try

    End Function

    Public Shared Function GetLoggedOnConsultantDeptID(ByVal UserName As String) As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try
            'Return (db.GetSQLScalar("select con_Department from l_consultant where con_ntusername =  '" & UserName & "'"))
            Return (db.GetSQLScalar("select con_Department from l_consultant where con_name =  '" & UserName & "'"))
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function GetConsultantNameNT(Optional ByVal UseGlobal As Boolean = False) As String
        ' get consultant name from db using their user logon
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Dim singleObject As Singleton = Singleton.Instance
            Dim conname As String
            If UseGlobal = True Then
                'conname = db.GetSQLScalar("select con_name from l_consultant where con_ntusername = '" & GlobalCRM.CurrentNTUserName.Trim & "'")
                conname = db.GetSQLScalar("select con_name from l_consultant where con_name = '" & singleObject.CurrentConName.Trim & "'")
            Else
                'conname = db.GetSQLScalar("select con_name from l_consultant where con_ntusername = '" & Environment.UserName.Trim & "'")
                conname = db.GetSQLScalar("select con_name from l_consultant where con_name = '" & singleObject.CurrentConName.Trim & "'")
                'If Not conname = String.Empty Then
                '    GlobalCRM.CurrentNTUserName = Environment.UserName
                'End If
            End If
            Return conname
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetConsultantId(Optional ByVal UseGlobal As Boolean = False) As String
        Return GlobalCRM.CurrentConID.ToString()
    End Function

    Public Shared Function GetConsultantCRMStatus(ByVal username As String) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("Select con_enablelogin from l_consultant where con_NTUsername  like '" & username.Trim & "'")
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function VerifyConsultantDetails(ByVal username As String) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLDataTable("Select con_name,con_password,con_id,con_email,con_emailpassword,Con_SecurityLevel from l_consultant where con_name like '" & username.ToString.Trim & "'")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function


    Public Shared Function GetConsultantStatus(ByVal username As String) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("Select con_status from l_consultant where con_NTUsername  like '" & username.Trim & "'")
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function BranchList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLDataTable("Select Branch_ID, Branch_Name from l_Branch")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function TaskList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLDataTable("Select TaskTemplate_ID, TaskTemplate_Name from D_TaskTemplates Order By TaskTemplate_ID")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function DepartmentList(Optional ByVal intBranchID As Integer = -1) As DataTable
        ' get list of departments
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                If intBranchID = -1 Then
                    Return db.GetSQLDataTable("Select Department_ID, Department_Name from L_Departments order by Department_name")
                Else
                    Return db.GetSQLDataTable("Select Department_ID, Department_Name from L_Departments where Department_BranchID = " & CStr(intBranchID) & "  order by Department_name ")
                End If
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function NationalityList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                .Append("select country_id,country_nationality from l_country order by country_nationality")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function LoadCaseStages(ByVal caseid As Long) As DataTable
        ' get list of Consultants
        Dim SQL As StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            SQL = New StringBuilder

            With SQL

                .Append("SELECT DISTINCT TOP 100 PERCENT DBo.L_StageLinks.StageLink_ToID as ID, DBo.L_StageLinks.StageLink_Desc as Descr ")
                .Append(" FROM DBo.L_StageLinks INNER JOIN")
                .Append(" DBo.L_Products ON DBo.L_StageLinks.StageLink_DiagramID = DBo.L_Products.Product_StageDiagramID INNER JOIN")
                .Append(" DBo.D_Case ON DBo.L_Products.Product_ID = DBo.D_Case.Case_ProductID")
                .Append(" WHERE(DBo.D_Case.Case_ID = " & caseid & ")")
                .Append(" ORDER BY DBo.L_StageLinks.StageLink_Desc")

                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function LoadPaymentMode() As DataTable
        ' get list of Consultants
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select PaymentP_id as id,PaymentP_Name as descr from l_PaymentPlans where PaymentP_Active=1")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetAddressText(ByVal AddressLinkId As Long) As String
        Dim SQL As StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim dt As DataTable
        Dim strReturn As String
        Try

            SQL = New StringBuilder
            With SQL
                .Append(" SELECT     a.Address_Text, a.Address_Region, a.Address_Country, a.Address_Postcode")
                .Append(" FROM         dbo.D_AddressLink AS l INNER JOIN")
                .Append(" dbo.D_Address AS a ON l.AddressLink_AddressID = a.Address_ID")
                .Append(" WHERE(l.AddressLink_ID = " & AddressLinkId & ")")

                dt = DB.GetSQLDataTable(.ToString)
                If Not dt Is Nothing Then
                    If dt.Rows.Count > 0 Then
                        strReturn = dt.Rows(0).Item("Address_Text").ToString & vbCrLf & dt.Rows(0).Item("Address_Region").ToString & vbCrLf & dt.Rows(0).Item("Address_Country").ToString & vbCrLf & dt.Rows(0).Item("Address_Postcode").ToString
                        Return strreturn
                    Else
                        Return String.Empty
                    End If
                Else
                    Return String.Empty
                End If
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
            strReturn = Nothing
            dt = Nothing
        End Try
    End Function

    Public Shared Function GetDocStoreSummary(ByVal CaseID As Long) As DocumentsSummary

        Dim SQL As StringBuilder
        Dim myDocs As New DocumentsSummary
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            SQL = New StringBuilder
            If CaseID > 0 Then

                With SQL
                    .Append("select count(*) from d_docreqs where docreq_caseid = " & CaseID)
                    myDocs.Required = DB.GetSQLScalar(.ToString)
                End With

                SQL = New StringBuilder

                With SQL
                    .Append("select count(*) from d_docreqs where docreq_caseid = " & CaseID & " and docreq_received = 1")
                    myDocs.Received = DB.GetSQLScalar(.ToString)
                End With

                SQL = New StringBuilder

                With SQL
                    .Append("select count(*) from d_docstore where docstore_caseid = " & CaseID)
                    myDocs.InDocStore = DB.GetSQLScalar(.ToString)
                End With
            End If

            Return myDocs

        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetSQLScalar(ByVal SQL As String) As Object

        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim returnObject As Object = Nothing
        Try
            returnObject = DB.GetSQLScalar(SQL)
            Return returnObject
        Catch ex As Exception
            returnObject = Nothing
            Return Nothing
        Finally
            DB = Nothing
        End Try

    End Function

    Public Shared Function AddPersonToCompany(ByVal CompanyID, ByVal RoleinCompany) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim tsk As New TasksEvents
        Dim dt As DataTable
        Dim LngDep As Long
        Dim StrData As String = String.Empty

        Try

            Dim SQL As New StringBuilder
            'See if any cases for this indivudal and if so cancel as otherwise problems could ensue.
            With SQL
                .Append("Select Case_ID from D_Case where Case_CandidateID = " & GlobalCRM.CurrentCandidateID)
                .Append(" AND Case_StatusID > 99 and Case_StatusID not in (1030, 1040)")
            End With

            dt = DB.GetSQLDataTable(SQL.ToString)

            If dt.Rows.Count > 0 Then

                'If Security._SecurityLevel <> SecurityLevel.AdminUser Then
                '    If MsgBox("This account has initiated cases, you must ensure that any payments or invoices raised on this or any prior case are appropriate to be migrated to the company account. Do you wish to proceed?", MsgBoxStyle.YesNo, "Admin") <> MsgBoxResult.Yes Then Exit Function
                'Else
                '    MsgBox("Only admins can upgrade private accounts with initiated cases", MsgBoxStyle.OkOnly, "Error")
                '    Exit Function
                'End If
            End If

            SQL = New StringBuilder
            'Check if this person is a dependant and if so warn that dependant link will be deleted
            SQL.Append("Select Dependant_ParentID from D_Dependants where Dependant_ChildID = " & GlobalCRM.CurrentCandidateID)
            dt = DB.GetSQLDataTable(SQL.ToString)
            If dt.Rows.Count > 0 Then
                'Yes they are a dependant, get details
                LngDep = dt.Rows(0).Item("Dependant_ParentID")

                SQL = New StringBuilder
                SQL.Append("Select Person_Fname, Person_Lname from D_Person where Person_ID = " & LngDep)
                dt = DB.GetSQLDataTable(SQL.ToString)
                If dt.Rows.Count = 0 Then
                    'Details missing, abort

                    'IntErr = GblFunc_Add_Audit("Data Error", StrModule, "Person no. " & LngDep & " listed as dependant parent, but record could not be found")
                    MsgBox("This person is listed as being a dependant, but the other persons record cannot be found. Action cancelled, please speak to the IT dept", , "Error")
                    Exit Function
                Else
                    Dim i As Integer = 0
                    Do
                        StrData = StrData & dt.Rows(i).Item("Person_fname") & " " & dt.Rows(i).Item("person_lname") & vbCrLf
                        i = i + 1
                    Loop While Not i >= dt.Rows.Count



                    If MsgBox("This person is a dependant of the following people" & vbCrLf & vbCrLf & StrData & vbCrLf & vbCrLf & "If you continue these links will be deleted, dependant of this person will not be affected." & vbCrLf & vbCrLf & "Are you sure you wish to proceed?", vbYesNo, "WARNING") <> 6 Then Exit Function
                End If
            Else
                LngDep = 0
            End If


            'Add person to company
            SQL = New StringBuilder
            SQL.Append("Update D_Person ")
            SQL.Append(" Set Person_CompanyID = " & CompanyID & "")
            SQL.Append(" , Person_CompanyRoleID = " & RoleinCompany & " ")
            SQL.Append(" WHERE Person_ID = " & GlobalCRM.CurrentCandidateID)

            If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function

            SQL = New StringBuilder
            SQL.Append(" Update D_Advertising set Advertising_LinkID = " & CompanyID & ", Advertising_LinkType = 1")
            SQL.Append(" Where Advertising_LinkID = " & GlobalCRM.CurrentCandidateID & " and Advertising_LinkType = 0")

            If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function
            SQL = New StringBuilder
            SQL.Append("Update D_Case set Case_CompanyID = " & CompanyID)
            SQL.Append(" Where Case_CandidateID = " & GlobalCRM.CurrentCandidateID)

            If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function

            SQL = New StringBuilder
            SQL.Append("Update A_Charges set Charge_CompanyID = " & CompanyID)
            SQL.Append(" Where Charge_PersonID = " & GlobalCRM.CurrentCandidateID)

            If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function
            SQL = New StringBuilder
            SQL.Append("Update A_Invoices set Invoice_CompanyID = " & CompanyID)
            SQL.Append(" Where Invoice_PersonID = " & GlobalCRM.CurrentCandidateID)

            If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function
            SQL = New StringBuilder
            SQL.Append("Update A_Payments set Payment_CompanyID = " & CompanyID)
            SQL.Append(" Where Payment_PersonID = " & GlobalCRM.CurrentCandidateID)

            If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function

            If LngDep <> 0 Then
                'Remove dependancy details
                SQL.Append("Delete from D_Depedants where Dependant_ChildID = " & GlobalCRM.CurrentCandidateID)
                If DB.ExecuteSQL(SQL.ToString) = False Then Exit Function
            End If


            tsk.GblFunc_Add_Event("Person Added", "Private individual no. " & GlobalCRM.CurrentCandidateID & " added to company", TasksEvents.EventStatus.SingleEvent, CompanyID, TasksEvents.EventTypes.EventCompany)

            Return True


        Catch ex As Exception
            ' CommonFunc.ErrorHandler(ex, "frmAddPersonToCompany.AddPersonToCompany", True)
        Finally
            DB = Nothing
            tsk = Nothing
        End Try
    End Function
    Public Shared Function getCompanyNameFromID(ByVal companyid As String) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            Return DB.GetSQLScalar("select company_name from d_company where company_id = '" & companyid.ToString & "'")
        Catch ex As Exception
            Return String.Empty
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function UpdatePaymentPlan(ByVal caseid As Long, ByVal PaymentPlan As Integer) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            If DB.ExecuteSQL("update d_case set Case_PaymentPlan = " & PaymentPlan & " where case_id = " & caseid) = True Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function GetCasePaymentPlan(ByVal Caseid As Long) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            Return DB.GetSQLScalar("select Case_PaymentPlan from d_case where case_id = " & Caseid)
        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function RoleList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select role_id,role_name from l_roles order by role_name")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function RelationshipList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            Return DB.GetSQLDataTable("Select Rel_ID, Rel_ChildName, Rel_ParentName from L_Relationships Order By Rel_ChildName")
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function CountryList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select country_id,country_name from l_country order by country_name")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try

    End Function
    Public Shared Function CountyList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select county_id,county_name from l_county order by county_name")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function WhereHearList() As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select wherehear_id,wherehear_text from l_wherehear order by wherehear_text")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function QualificationList() As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder


            With SQL
                .Append("select qual_id,qual_name from l_qualifications order by qual_name")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function

    Public Shared Function UKVisaList() As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select ukvisa_id,ukvisa_name from l_ukvisa order by ukvisa_name")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function TitleList(Optional ByVal StrTitle As String = "") As DataTable

        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                If StrTitle <> "" Then
                    .Append("select title_id,title_text, title_discontinued from l_titles where Title_Discontinued = 0 or Title_Text='" & StrTitle & "' order by title_text")
                Else
                    .Append("select title_id,title_text, title_discontinued from l_titles where Title_Discontinued = 0 order by title_text")
                End If
                Return DB.GetSQLDataTable(.ToString)
                'objDT = CommonFunc.FindDiscontinued(objDT, "title_text", "title_discontinued")

            End With
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function EmailFrom() As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim Name As String = String.Empty
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            SQL.Append("Select Con_EMail from L_Consultant where Con_ID = " & GlobalCRM.CurrentConID)
            Name = DB.GetSQLScalar(SQL.ToString)
            Return Name

        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetCaseProductType(ByVal lngCaseID As Long) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL


                .Append("SELECT dbo.L_ProductTypes.ProductType_Name ")
                .Append(" FROM dbo.D_Case INNER JOIN ")
                .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID ")
                .Append(" WHERE(dbo.D_Case.Case_ID = " & CStr(lngCaseID) & ")")

                Return DB.GetSQLScalar(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function WriteOffFinance(ByVal caseid As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim txtMsg As StringBuilder
            Dim PaidAmt, GrossInv As Object
            Dim GVFee, GVFeeNet, writeoffamount, RemFee, NetInvoiced, VATbleAmt As Double
            Dim dt As DataTable
            Dim txtNetInv As String
            Dim row As DataRow
            Dim sSplit As Integer
            Dim blnwriteOffwithVAT As Boolean = False
            SQL = New StringBuilder
            Dim Charges() As Long = Nothing
            Dim InvoiceNo As Long = 0
            Dim VATPercent As Double
            SQL.Append("Select sum(Payment_Amount) FROM A_Payments WHERE Payment_CaseID = " & caseid)
            PaidAmt = DB.GetSQLScalar(SQL.ToString)
            If PaidAmt.ToString = "System.DBNull" Or PaidAmt.ToString = String.Empty Then
                PaidAmt = 0
            End If
            PaidAmt = Math.Round(PaidAmt, 2)
            SQL = New StringBuilder
            SQL.Append("SELECT ROUND(GrossInv, 2) FROM dbo.V_Case WHERE Case_ID = " & caseid)
            GrossInv = DB.GetSQLScalar(SQL.ToString)
            GrossInv = Math.Round(GrossInv, 2)

            SQL = New StringBuilder
            With SQL
                .Append("SELECT     TOP 100 PERCENT SUM(dbo.A_Charges.Charge_Amount) AS Expr1 FROM dbo.A_Charges LEFT OUTER JOIN")
                .Append("  dbo.A_ChargeCategories ON dbo.A_Charges.Charge_Category = dbo.A_ChargeCategories.ChargeCat_ID ")
                .Append(" WHERE     (dbo.A_Charges.Charge_CaseID = " & caseid & ") AND (dbo.A_Charges.Charge_InvoiceID <> 0)")
            End With

            txtNetInv = (DB.GetSQLScalar(SQL.ToString).ToString)
            If txtNetInv <> "" Then
                NetInvoiced = CDbl(txtNetInv)
                NetInvoiced = Math.Round(NetInvoiced, 2)
            Else
                NetInvoiced = 0
            End If


            SQL = New StringBuilder
            With SQL
                .Append(" SELECT     TOP 100 PERCENT CAST(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * (dbo.A_Charges.Charge_VAT / 100) AS money) ")
                .Append(" AS ChgGross, dbo.A_Charges.Charge_Amount, dbo.A_Charges.Charge_VAT FROM dbo.A_Charges LEFT OUTER JOIN ")
                .Append(" dbo.A_ChargeCategories ON dbo.A_Charges.Charge_Category = dbo.A_ChargeCategories.ChargeCat_ID WHERE (dbo.A_Charges.Charge_CaseID = " & caseid)
                .Append(" ) AND (dbo.A_Charges.Charge_InvoiceID <> 0) AND (dbo.A_Charges.Charge_Category = 7 OR dbo.A_Charges.Charge_Category IN (SELECT dbo.A_ChargeTypes.ChargeType_ID ")
                .Append(" FROM dbo.A_ChargeTypes INNER JOIN dbo.A_ChargeLinks ON dbo.A_ChargeTypes.ChargeType_ID = dbo.A_ChargeLinks.ChargeLink_ChargeTypeID INNER JOIN dbo.L_Products ON ")
                .Append(" dbo.A_ChargeLinks.ChargeLink_ChargeGroupID = dbo.L_Products.Product_ChargeGroupID INNER JOIN ")
                .Append(" dbo.D_Case ON dbo.L_Products.Product_ID = dbo.D_Case.Case_ProductID WHERE (dbo.A_ChargeTypes.ChargeType_CategoryID = 7) AND (dbo.D_Case.Case_ID = " & caseid & ")))")
            End With
            dt = DB.GetSQLDataTable(SQL.ToString)
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    VATPercent = CDbl(dt.Rows(0).Item("charge_vat").ToString)
                    For Each row In dt.Rows

                        GVFee = CDbl(GVFee) + CDbl(row.Item(0).ToString)
                        If CDbl(row.Item("charge_vat").ToString) > 0 Then
                            GVFeeNet = CDbl(GVFeeNet) + CDbl(row.Item("Charge_Amount").ToString)
                        End If
                    Next
                End If
            End If
            GVFee = Math.Round(GVFee, 2)
            GVFeeNet = Math.Round(GVFeeNet, 2)
            Dim txtwriteoff As String = String.Empty
            RemFee = CDbl(GrossInv) - GVFee
            RemFee = Math.Round(RemFee, 2)

            If (CDbl(PaidAmt)) <= (CDbl(GVFee)) Then
                writeoffamount = (GVFee - PaidAmt) + RemFee '- ((NetInvoiced) * 17.5 / 100)
                blnwriteOffwithVAT = True
                If RemFee > 0 Then
                    sSplit = 1
                End If
                VATbleAmt = Math.Round(CDbl(GVFee - PaidAmt), 2)
                writeoffamount = Math.Round(writeoffamount, 2)
                'writeoffamount = Math.Round(CDbl((writeoffamount * 100) / (100 + VATPercent)), 2)
                txtwriteoff = "(" & GVFee & "-" & PaidAmt & ") = " & VATbleAmt & "- With VAT. " & vbCrLf & RemFee & " - Without VAT" '& " - ((" & NetInvoiced & ") * 17.5 / 100) " & " = " & writeoffamount.ToString

            ElseIf (CDbl(PaidAmt)) > (CDbl(GVFee)) Then
                writeoffamount = (GrossInv - PaidAmt)
                blnwriteOffwithVAT = False
                writeoffamount = Math.Round(writeoffamount, 2)
                txtwriteoff = GrossInv & "-" & PaidAmt & " = " & writeoffamount.ToString
            End If

            If writeoffamount <> 0 Then
                txtMsg = New StringBuilder
                With txtMsg
                    .Append("Gross Invoiced amount : " & GrossInv & vbCrLf)
                    .Append("Net Invoiced amount : " & NetInvoiced & vbCrLf)
                    .Append("Global Visa Fee Invoiced : " & GVFee & vbCrLf)
                    .Append("Global Visa Fee Invoiced : " & GVFeeNet & vbCrLf)
                    .Append("Other fee : " & RemFee & vbCrLf)
                    .Append("-----------------------------------------------------" & vbCrLf)
                    .Append("Total amount paid : " & PaidAmt & vbCrLf)
                    .Append("-----------------------------------------------------" & vbCrLf)
                    .Append(txtwriteoff & vbCrLf)
                    .Append("Amount to be written off : " & writeoffamount & vbCrLf)
                    .Append("-----------------------------------------------------" & vbCrLf)
                    .Append(vbCrLf & vbCrLf)
                    .Append("Pushing the button 'NO' would allow you to enter an amount to writeoff" & vbCrLf)
                End With
                If CommonFuncs.DisplayyesNoMessage(txtMsg.ToString, MsgBoxStyle.YesNo, "Writeoff") = 1 Then
                    If sSplit = 1 Then
                        FinanceWO(RemFee)
                        If VATbleAmt > 0 Then
                            FinanceWO(VATbleAmt, VATPercent, blnwriteOffwithVAT)
                        End If
                    ElseIf sSplit = 0 Then
                        FinanceWO(writeoffamount, VATPercent, blnwriteOffwithVAT)
                    End If

                    Return True
                Else
                    Dim swriteoff As Double = Val(InputBox("Please enter an amount to writeoff : ", "Write Off"))
                    If swriteoff > 0 Then
                        FinanceWO(swriteoff)
                        Return True
                    End If
                End If
                'Close all the open tasks for that case.
            Else
                CommonFuncs.DisplayMessage("The amount to be written off is " & writeoffamount & " . Hence the file is not written off", CommonDialog.CustomDialogIcons.Information, "Zero")

                Return False
            End If
        Catch ex As Exception
        Finally
            DB = Nothing
            SQL = Nothing
        End Try

    End Function
    Public Shared Function FinanceWO(ByVal writeoffamount As Double, Optional ByVal VATPercent As Double = 0, Optional ByVal blnwriteOffwithVAT As Boolean = False) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim InvoiceNo As Long
            Dim Charges() As Long
            SQL = New StringBuilder
            Dim FinalWO As Double = Math.Round(CDbl((writeoffamount * 100) / (100 + VATPercent)), 2)
            With SQL
                .Append("Insert Into A_Charges (Charge_Type, Charge_EnteredBy, Charge_CompanyID, Charge_CaseID, Charge_PersonID, Charge_VAT, Charge_Amount, Charge_TPCost, Charge_Category) VALUES (")
                .Append("'Writeoff','" & GlobalCRM.CurrentConName)
                .Append("'," & GlobalCRM.CurrentCompanyID & "," & GlobalCRM.CurrentCaseID & ",")
                .Append(GlobalCRM.CurrentCandidateID & "," & VATPercent & "," & ((-1) * FinalWO) & ", 0.0" & ", 0)")
            End With
            Dim Id As Long = DB.ExecuteSQL(SQL.ToString, True)
            '= DB.GetIdentity
            If Id > 0 Then
                ReDim Preserve Charges(0)
                Charges(0) = Id
                InvoiceNo = Accounts.CreateInvoice(GlobalCRM.CurrentCaseID, GlobalCRM.CurrentCompanyID, GlobalCRM.CurrentCandidateID, Date.Today, GlobalCRM.CurrentConName)
                If Not Accounts.UpdateChargesAsInvoiced(GlobalCRM.CurrentCaseID, InvoiceNo, Charges, False) Then
                    Exit Function
                End If
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
        Finally
            DB = Nothing
            SQL = Nothing
        End Try

    End Function
    Public Shared Function getCaseStageTitle(ByVal caseid As Long) As String
        Dim DB As New Global_Visas_Data_Layer.DataAccess()

        Try
            Return DB.GetSQLScalar("SELECT dbo.L_CaseStages.CaseStage_Title FROM   dbo.D_Case INNER JOIN  dbo.L_CaseStages ON dbo.D_Case.Case_StatusID = dbo.L_CaseStages.CaseStage_Number WHERE (dbo.D_Case.Case_ID = " & caseid & ")")
        Catch ex As Exception
            Return String.Empty
        Finally
            DB = Nothing

        End Try
    End Function
    Public Shared Function UpdateCaseStatus(ByVal StageLinkFrom As String, ByVal StageLinkTo As String) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("insert into d_caseStageHistory(Case_Id,CaseStage_From,CaseStage_To,Cons_ID,Case_DTSet ) ")
                .Append("values(" & GlobalCRM.CurrentCaseID & ",'" & StageLinkFrom & "','" & StageLinkTo & "'," & GlobalCRM.CurrentConID & ",getdate())")
            End With
            If DB.ExecuteSQL(SQL.ToString) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function UpdateCaseStatusNo(ByVal StageLinkFrom As String, ByVal StageLinkTo As String) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("insert into D_CaseStatusHistory(Case_Id,Casestatus_Fromid,Casestatus_ToID,case_SetByConsultantID,Case_DTSet ) ")
                .Append("values(" & GlobalCRM.CurrentCaseID & ",'" & StageLinkFrom & "','" & StageLinkTo & "'," & GlobalCRM.CurrentConID & ",getdate())")
            End With
            If DB.ExecuteSQL(SQL.ToString) Then
                Return True
            Else
                Return False

            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function CompleteTasksForCase(ByVal caseid As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim tsk As New TasksEvents
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            Dim dt As DataTable

            SQL.Append("select event_id from d_event where event_category = 2 and Event_LinkID = " & caseid & " and event_completedby is null")
            dt = DB.GetSQLDataTable(SQL.ToString)
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    Dim EventIds As String = CommonFuncs.ManipulateDT(dt)

                    Dim Msg As String = "Auto Close" & vbCrLf & "---------------------------------------------------------" & vbCrLf & "Completion message" & vbCrLf & vbCrLf & "The task is completed as the case status has been set to close/cancel/writeoff"
                    SQL = New StringBuilder
                    SQL.Append("UPDATE D_Event  SET Event_Category = 3, Event_CompletedBy = '" & GlobalCRM.CurrentConID & "', Event_DTCompleted = getdate(), Event_message = '" & Msg & " ' WHERE Event_ID in(" & EventIds & ")")

                    If DB.ExecuteSQL(SQL.ToString) = True Then
                        'add event that all the tasks are closed fo the case
                        tsk.GblFunc_Add_Event("Tasks Completed", "All the open tasks associated with this case has been closed now.", TasksEvents.EventStatus.SingleEvent, caseid, TasksEvents.EventTypes.EventCase)
                    End If
                End If
            End If
            ' WHERE Event_ID = 4305427
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
            SQL = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function GetStatusFromCase(ByVal caseid) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try

            Return db.GetSQLScalar("select case_statusid from d_case where case_id = " & caseid)

        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing

        End Try
    End Function
    Public Shared Function UpdateStatusAsWriteoff(ByVal caseid) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try

            If db.ExecuteSQL("Update d_case set case_statusid = 2000, writeoff = 1,Case_DTWrittenOff = getdate() where case_id = " & caseid) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing

        End Try
    End Function
    Public Shared Function RevertWriteOffAmount(ByVal caseid As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim sql As New StringBuilder
        Try

            Dim msg As StringBuilder = Nothing
            Dim dt As DataTable
            Dim i As Integer
            Dim chrID As String = String.Empty
            With sql
                .Append("SELECT     TOP 100 PERCENT dbo.A_Charges.Charge_ID, dbo.A_Charges.Charge_InvoiceID, dbo.A_Charges.Charge_Type, ")
                .Append(" ISNULL(dbo.A_ChargeCategories.ChargeCat_Name, '') AS Category, dbo.A_Charges.Charge_Amount, dbo.A_Charges.Charge_VAT, ")
                .Append(" dbo.A_Charges.Charge_TPCost, dbo.A_Charges.Charge_Amount - dbo.A_Charges.Charge_TPCost AS ChgIncome, ")
                .Append(" CAST(dbo.A_Charges.Charge_Amount + dbo.A_Charges.Charge_Amount * (dbo.A_Charges.Charge_VAT / 100) AS money) AS ChgGross")
                .Append(" FROM         dbo.A_Charges LEFT OUTER JOIN")
                .Append(" dbo.A_ChargeCategories ON dbo.A_Charges.Charge_Category = dbo.A_ChargeCategories.ChargeCat_ID")
                .Append(" WHERE     (dbo.A_Charges.Charge_CaseID = " & caseid & ") AND (dbo.A_Charges.Charge_InvoiceID <> 0) AND (dbo.A_Charges.Charge_Type = 'writeoff')")
                .Append(" ORDER BY dbo.A_Charges.Charge_InvoiceID, dbo.A_Charges.Charge_ID")
                dt = db.GetSQLDataTable(.ToString)
            End With
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    msg = New StringBuilder

                    For i = 0 To dt.Rows.Count - 1
                        With msg
                            .Append("Charge ID : " & dt.Rows(i).Item("Charge_ID"))
                            If chrID = String.Empty Then
                                chrID = dt.Rows(i).Item("Charge_ID").ToString
                            Else
                                chrID = chrID & "," & dt.Rows(i).Item("Charge_ID").ToString
                            End If
                            .Append(vbCrLf)
                            .Append("Charge Invoice ID : " & dt.Rows(i).Item("Charge_InvoiceID"))
                            .Append(vbCrLf)
                            .Append("Charge Type : " & dt.Rows(i).Item("Charge_Type"))
                            .Append(vbCrLf)
                            .Append("Charge Category Name : " & dt.Rows(i).Item("Category"))
                            .Append(vbCrLf)
                            .Append("Charge Amount : " & dt.Rows(i).Item("Charge_Amount"))
                            .Append(vbCrLf)
                            .Append("Charge VAT : " & dt.Rows(i).Item("Charge_VAT"))
                            .Append(vbCrLf)
                            .Append("Charge TP Cost : " & dt.Rows(i).Item("Charge_TPCost"))
                            .Append(vbCrLf)
                            .Append("Charge Income : " & dt.Rows(i).Item("ChgIncome"))
                            .Append(vbCrLf)
                            .Append("Charge Gross : " & dt.Rows(i).Item("ChgGross"))
                            .Append(vbCrLf & vbCrLf & vbCrLf)
                        End With
                    Next
                End If
            End If
            If chrID <> String.Empty Then

                sql = New StringBuilder
                sql.Append("delete from a_charges where charge_id in(" & chrID & ")")
                If db.ExecuteSQL(sql.ToString) Then
                    tsk.GblFunc_Add_Event("Finance Reverted", msg.ToString, TasksEvents.EventStatus.SingleEvent, caseid, TasksEvents.EventTypes.EventCase)
                    Return True
                Else
                    Return False
                End If
            Else
                tsk.GblFunc_Add_Event("Finance Reverted", "No Writeoff charges to be reverted", TasksEvents.EventStatus.SingleEvent, caseid, TasksEvents.EventTypes.EventCase)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Function
    Public Shared Function SetCaseStage(ByVal caseid As Long, ByVal stageid As Long, ByVal stagedesc As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder
            sql.Append("update d_case set case_statusid = " & stageid & " where case_id =" & caseid)
            If db.ExecuteSQL(sql.ToString) Then
                tsk.GblFunc_Add_Event("Case Stage Set", "The case has been assigned to " & stagedesc, TasksEvents.EventStatus.SingleEvent, caseid, TasksEvents.EventTypes.EventCase)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
            sql = Nothing
        End Try
    End Function
    Public Shared Function AssignToConsultant(ByVal caseid As Long, ByVal conid As Long, ByVal conname As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder
            sql.Append("update d_case set case_concwid = " & conid & " where case_id =" & caseid)
            If db.ExecuteSQL(sql.ToString) Then
                tsk.GblFunc_Add_Event("Case Re-Assigned", "The case has been assigned to " & conname, TasksEvents.EventStatus.SingleEvent, caseid, TasksEvents.EventTypes.EventCase)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
            sql = Nothing
        End Try
    End Function
    Public Shared Function RevertWriteOff(ByVal caseid As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As TasksEvents
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder
            sql.Append("update d_case set writeoff =0, case_dtwrittenoff =null where case_id =" & caseid)
            If db.ExecuteSQL(sql.ToString) Then
                tsk.GblFunc_Add_Event("Write Off Reverted", "The case has been put back to working mode", TasksEvents.EventStatus.SingleEvent, caseid, TasksEvents.EventTypes.EventCase)
                Return True
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            tsk = Nothing
            sql = Nothing
        End Try
    End Function
    Public Shared Function DaysTasks(ByVal Today As Date) As DataTable
        ' get tasks for current day
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder

            With sql
                .Append("SELECT D_Event.Event_LinkID, D_Event.Event_LinkType, Case D_Event.Event_VIP when 1 then 'URGENT:' else '' end + D_Event.Event_Description as Description,")
                .Append("Case D_Event.Event_LinkType  When 1 then isnull(product_name,'Unspecified product') + ' for ' + isnull(D_person_candidate.Person_fname + ' ' + D_person_candidate.Person_lname,'Unspecified candidate') When 2 then D_Person.Person_FName + ' ' + D_person.Person_Lname  When 3 then Company_Name  When 4 then 'User Task'  End as LinkDesc  FROM D_Event LEFT OUTER JOIN D_Company ON D_Event.Event_LinkID = D_Company.Company_ID LEFT OUTER JOIN D_Person ON D_Event.Event_LinkID = D_Person.Person_ID Left Outer Join D_Case ON D_Event.Event_LinkID = D_Case.Case_ID LEFT OUTER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID LEFT OUTER JOIN D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID WHERE (D_Event.Event_Category = 2) AND (D_Event.Event_Scheduledfor = " & GlobalCRM.CurrentConViewID & ") ")
                '.Append("AND (D_Event.Event_DTScheduled = '" & Format(Today, "dd-MMM-yyyy") & "') ORDER BY Event_Vip Desc")
                .Append("AND (Convert(DateTime,Convert(varchar(15),D_Event.Event_DTScheduled,112)) = '" & Format(Today, "dd-MMM-yyyy") & "') ORDER BY Event_Vip Desc")

            End With
            Return db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

            sql = Nothing
        End Try
    End Function

    Public Shared Function GetSearchHistory(ByVal conid As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder

            With sql
                .Append("Select Search_CompanyName, Search_Surname, Case Search_type when 1 then 'Name' when 2 then 'E-Mail' when 3 then 'Ext Ref' when 4 then 'DoB' end as SType,Search_count, Search_DT, Search_Type ")
                .Append(" From D_UserSearch")
                .Append(" Where Search_UserID = " & conid)
                .Append(" Order by Search_ID Desc")
            End With

            Return db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

            sql = Nothing
        End Try
    End Function
    Public Shared Function GetAddressBook(ByVal AddressType As AddressTypes) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim sql As StringBuilder
        Try
            sql = New StringBuilder

            With sql

                Select Case AddressType

                    Case AddressTypes.GV

                        .Append("Select con_name as personname, Con_Email as EmailAddress from L_Consultant ")
                        .Append(" WHERE Con_Email <> '' and Con_Status = 1")
                        .Append(" ORDER BY con_name")


                    Case AddressTypes.Client

                        If GlobalCRM.CurrentCompanyID > 0 Then
                            .Append("SELECT D_Person.Person_FName + ' ' + D_Person.Person_LName AS PersonName, D_EmailAddress.EMailAddress_Address as EmailAddress")
                            .Append(" FROM D_EmailAddress INNER JOIN")
                            .Append(" D_Person ON D_EmailAddress.EMailAddress_PersonID = D_Person.Person_ID")
                            .Append(" WHERE (D_EmailAddress.EMailAddress_Type = 1) And (D_Person.Person_CompanyID = " & GlobalCRM.CurrentCompanyID & ")")
                            .Append(" ORDER BY D_Person.Person_FName + ' ' + D_Person.Person_LName")
                        Else
                            .Append("Select EmailAddress_Name, EMailAddress_Address from D_EmailAddress ")
                            .Append(" WHERE EmailAddress_PersonID = " & GlobalCRM.CurrentCandidateID)
                            .Append(" ORDER BY EMailAddress_Name")
                        End If

                    Case AddressTypes.System

                        .Append("Select AddressBook_Name as personname, AddressBook_Address from D_AddressBook")
                        .Append(" ORDER BY AddressBook_Name")

                End Select

            End With

            Return db.GetSQLDataTable(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

            sql = Nothing
        End Try
    End Function
    Public Shared Function GblFunc_Get_Sigfile(ByVal ConID As Integer, Optional ByVal bJustDisclaimer As Boolean = False) As String

        Dim myCon As ConsultantInfo = GetConsultantInfo(ConID)
        Dim mySig As New StringBuilder
        Dim Disclaimer As String = String.Empty
        Dim myBranch As BranchRecord = Branch.GetBranch(myCon.BranchID)
        Dim fax As String
        With mySig

            If bJustDisclaimer = False Then
                'Block 1
                .Append(myCon.ConsultantName & vbCrLf)
                .Append(myCon.JobTitle & vbCrLf)
                If myCon.Accreditation.Trim <> String.Empty Then
                    .Append(myCon.Accreditation)
                End If

                .Append(vbCrLf)

                'Block 2
                '.Append("Permit & Visas" & vbCrLf)
                .Append(Branch.FormatBranch(myBranch) & vbCrLf)

                .Append(Branch.GetCountryName(myBranch.Branch_CountryID))

                .Append(vbCrLf & vbCrLf)

                'Block 3
                Dim tel As String = myCon.DDI
                If tel = String.Empty Then
                    tel = myBranch.Branch_TelNo
                End If
                .Append("Tel: " & tel & vbCrLf)

                fax = myCon.Fax.ToString
                If fax = String.Empty Then
                    fax = myBranch.Branch_Fax.ToString
                End If
                .Append("Fax: " & fax & vbCrLf)
                .Append(myBranch.Branch_Website)

                .Append(vbCrLf & vbCrLf)
            End If

            'Block 4
            .Append("***********************************************************************" & vbCrLf)
            .Append(GetDisclaimer(myCon.Dislaimer))
            .Append("Please note " & vbCrLf)

            .Append("This e-mail and any file transmitted with it are confidential and may be legally privileged. It is intended solely for the addressee and may not be disclosed to or used by anyone other than the addressee. If you have received this e-mail in error, please advise the sender immediately. Any views or opinions expressed in this message are those of the author and do not necessarily represent those of Permits and Visas. No reliance may be placed on this message without written confirmation from an authorised representative of its contents. Permits and Visas employs consultants worldwide registered with all the relevant governing bodies. Contact your personal consultant for full details of individual qualifications. ")

            .Append(vbCrLf)
            .Append(vbCrLf & "***********************************************************************")

        End With

        Return mySig.ToString

    End Function
    Public Shared Function GetDisclaimer(ByVal DisclaimerId As Integer) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try
            Dim Disclaimer As String = db.GetSQLScalar("Select disclaimer_text from L_Disclaimer where Disclaimer_ID = " & DisclaimerId)

            If Disclaimer.Trim = String.Empty Then
                Disclaimer = db.GetSQLScalar("Select disclaimer_text from L_Disclaimer where Disclaimer_ID = 1")
            End If

            If Disclaimer.Trim = String.Empty Then
                Disclaimer = "This e-mail and any file transmitted with it are confidential and may be legally privileged. It is intended solely for the addressee and may not be disclosed to or used by anyone other than the addressee. If you have received this e-mail in error, please advise the sender immediately."
            End If

            Return Disclaimer
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function
    'Public Shared Function GetEmailTemplateList(ByVal CaseId As Long, ByVal CompanyId As Long) As DataTable
    '    Dim DB As New Global_Visas_Data_Layer.DataAccess()
    '    Dim SLSId As Integer = CommonDataFunctions.GblFunc_Get_Dataset(CaseId, 3)
    '    Dim SQL As New StringBuilder
    '    Dim IntCo As Integer
    '    Dim myStage As Cases.CaseStage

    '    If SLSId = 0 Then
    '        SLSId = 34
    '    End If

    '    If CompanyId > 0 Then
    '        IntCo = 2
    '    Else
    '        IntCo = 1
    '    End If

    '    myStage = Cases.GetCaseStage(CaseId)

    '    If myStage.StageNumber = 0 Then
    '        myStage.StageNumber = 10
    '    End If

    '    With SQL
    '        .Append("SELECT distinct D_EmailTemplates.EMailTemplate_ID, D_EmailTemplates.EMailTemplate_Name")

    '        .Append(" FROM D_EmailTemplates INNER JOIN")
    '        .Append(" I_SL_SLSMailTemplates ON D_EmailTemplates.EMailTemplate_ID = I_SL_SLSMailTemplates.SLSMailTemplate_TemplateID")

    '        .Append(" WHERE (I_SL_SLSMailTemplates.SLSMailTemplate_SLSID = " & SLSId & ") ")
    '        .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_StageEnable <= " & myStage.StageNumber & ") ")
    '        .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_StageDisable > " & myStage.StageNumber & ") ")
    '        .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_CustomerType IN (0, " & IntCo & "))")

    '        .Append(" ORDER BY EMailTemplate_Name")
    '    End With

    '    Return DB.GetSQLDataTable(SQL.ToString)

    'End Function

    Public Shared Function GetEmailTemplateList(ByVal CaseId As Long, ByVal CompanyId As Long) As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim SQL As StringBuilder
        Try


            Dim SLSId As Integer = GblFunc_Get_Dataset(CaseId, 3)

            Dim IntCo As Integer
            Dim myStage As Cases.CaseStage

            If SLSId = 0 Then
                SLSId = 34
            End If

            If CompanyId > 0 Then
                IntCo = 2
            Else
                IntCo = 1
            End If

            myStage = Cases.GetCaseStage(CaseId)

            If myStage.StageNumber = 0 Then
                myStage.StageNumber = 10
            End If
            SQL = New StringBuilder
            With SQL
                .Append("SELECT distinct D_EmailTemplates.EMailTemplate_ID, D_EmailTemplates.EMailTemplate_Name")

                .Append(" FROM D_EmailTemplates INNER JOIN")
                .Append(" I_SL_SLSMailTemplates ON D_EmailTemplates.EMailTemplate_ID = I_SL_SLSMailTemplates.SLSMailTemplate_TemplateID")

                .Append(" WHERE (I_SL_SLSMailTemplates.SLSMailTemplate_SLSID = " & SLSId & ") ")
                .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_StageEnable <= " & myStage.StageNumber & ") ")
                .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_StageDisable > " & myStage.StageNumber & ") ")
                .Append(" AND (I_SL_SLSMailTemplates.SLSMailTemplate_CustomerType IN (0, " & IntCo & "))")

                .Append(" ORDER BY EMailTemplate_Name")
            End With

            Return DB.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    'Public Shared Function GetEmailTemplate(ByVal TemplateId As Integer) As EmailTemplate
    '    Dim DB As New Global_Visas_Data_Layer.DataAccess()
    '    Dim SQL As StringBuilder
    '    Dim myData As DataTable
    '    Try
    '        GetEmailTemplate = Nothing

    '        myData = DB.GetSQLDataTable("Select EmailTemplate_subjectline, EmailTemplate_Body from D_EmailTemplates where EmailTemplate_id = " & TemplateId)

    '        If Not IsNothing(myData) Then
    '            If myData.Rows.Count > 0 Then
    '                Dim myTemp As New EmailTemplate

    '                myTemp.Subject = IIf(IsDBNull(myData.Rows(0).Item("emailtemplate_subjectline")), "", myData.Rows(0).Item("emailtemplate_subjectline"))
    '                myTemp.Body = IIf(IsDBNull(myData.Rows(0).Item("emailtemplate_body")), "", myData.Rows(0).Item("emailtemplate_body"))

    '                Return myTemp

    '            End If
    '        End If
    '    Catch ex As Exception
    '        Return Nothing
    '    Finally
    '        DB = Nothing
    '        SQL = Nothing
    '        myData = Nothing
    '    End Try
    'End Function


    Public Shared Function SendEmailToClient_ForLoginArea(ByVal PersonId As Long, ByVal EmailToType As EmailType) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Try

            Dim sqlbldr As New StringBuilder
            Dim EmailFrom, strRep As String
            Dim OpToEmail As String
            Dim strTemp As String
            Dim TemplateID As Integer
            Dim blnSignature As Boolean = True
            Dim CommonFuncs As New CommonFuncs
            TemplateID = GetCustomizationValue("LoginAreaAnswer_Cand")

            ' TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_LoginArea_Answer", GetType(System.Int16)), Integer)
            If TemplateID <> 0 Then
                'Dim EmailFrom As String
                Dim EmailTo As String

                If EmailFrom = String.Empty Then
                    'sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & GlobalCRM.CurrentConID)
                    EmailFrom = "client-services@Globalvisas.com" 'DB.GetSQLScalar(sqlbldr.ToString)
                End If
                If EmailFrom Is Nothing Then
                    CommonFuncs.DisplayMessage("Your email id is not updated in the database." & vbCrLf & "Please contact IT", CommonDialog.CustomDialogIcons.Information, "Email Not sent")
                    Exit Function

                End If

                Dim DataType As Integer
                Dim DataID As Long
                If GlobalCRM.CurrentCompanyID = 0 Then
                    DataType = 1
                    DataID = GlobalCRM.CurrentCaseID
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & PersonId)
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        CommonFuncs.DisplayMessage("The Email address of the client is not found in the system. Hence email has not been sent", CommonDialog.CustomDialogIcons.Information, "Email Not sent")
                        Exit Function
                    End If
                Else
                    DataType = 3
                    DataID = GlobalCRM.CurrentCompanyID
                    'Fetch the company's contact and send email to associated ocntact and not the candidate

                    If EmailToType = EmailType.Contact Then
                        sqlbldr = New StringBuilder
                        sqlbldr.Append("SELECT     dbo.D_EmailAddress.EMailAddress_Address FROM   dbo.D_Case INNER JOIN   dbo.D_EmailAddress ON dbo.D_Case.Case_ContactID = dbo.D_EmailAddress.EMailAddress_PersonID WHERE     (dbo.D_Case.Case_ID = " & GlobalCRM.CurrentCaseID & ")")
                        EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    End If
                    If EmailToType = EmailType.Candidate Then
                        sqlbldr = New StringBuilder
                        sqlbldr.Append("SELECT     dbo.D_EmailAddress.EMailAddress_Address FROM   dbo.D_Case INNER JOIN   dbo.D_EmailAddress ON dbo.D_Case.Case_CandidateID = dbo.D_EmailAddress.EMailAddress_PersonID WHERE     (dbo.D_Case.Case_ID = " & GlobalCRM.CurrentCaseID & ")")
                        EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    End If

                    If EmailTo Is Nothing Then
                        CommonFuncs.DisplayMessage("The Email address of the client is not found in the system. Hence email has not been sent", CommonDialog.CustomDialogIcons.Information, "Email Not sent")
                        Exit Function
                    End If
                End If
                Dim dt As DataTable

                Dim myTemplate As EmailTemplateInfo
                myTemplate = GetEmailTemplate(TemplateID)
                Dim StrSQL As String = String.Empty

                Dim StrSQLBookmarks As String = String.Empty
                Dim intCount As Integer = 1
                Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
                Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

                Dim oWrite As System.IO.StreamWriter
                oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
                oWrite.Write(myTemplate.Body)
                oWrite.Close()

                Dim oRead As System.IO.StreamReader
                Dim LineIn As String
                Dim strNew() As String
                Dim strnew1() As String
                Dim i As Integer
                Dim c As New Collection
                Dim cData As Collection = Nothing
                Dim cIndex As New Collection
                Dim bln As Boolean
                oRead = IO.File.OpenText(txtfile)
                While oRead.Peek <> -1
                    LineIn = oRead.ReadLine()
                    strNew = Split(LineIn, "[")
                    For i = 0 To strNew.Length - 1
                        strnew1 = Split(strNew(i).ToString, "]")
                        If strnew1.Length > 1 Then
                            'StrSQLBookmarks = strnew1(0).ToString
                            StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
                            If cIndex.Contains(strnew1(0).ToString) = False Then
                                cIndex.Add(i, strnew1(0).ToString)
                                bln = True
                            End If
                            If bln Then
                                c.Add(strnew1(0).ToString)
                            End If
                            bln = False
                        End If
                    Next
                End While
                oRead.Close()
                If Len(StrSQLBookmarks) > 0 Then
                    StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
                    StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

                    Dim sql1 As New StringBuilder
                    With sql1
                        .Append("SELECT BookMark_Text as BMName")
                        .Append(" FROM I_SL_Bookmarks")
                        .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
                        '.Append(" AND Bookmark_VersionID <= " & 1)
                        .Append(" GROUP BY BookMark_Text")
                    End With
                    dt = db.GetSQLDataTable(sql1.ToString)
                    If Not dt Is Nothing Then
                        If dt.Rows.Count > 0 Then
                            Dim StrBMData As String = String.Empty
                            cData = New Collection
                            For i = 0 To dt.Rows.Count - 1
                                ' So we can monitor value
                                strTemp = dt.Rows(i).Item("BMName")
                                If dt.Rows(i).Item("BMName") <> "" Then
                                    If GlobalCRM.CurrentCompanyID <> 0 Then
                                        If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                            StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
                                            cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                        Else
                                            DataType = 1
                                            DataID = GlobalCRM.CurrentCaseID
                                            StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                            cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                        End If
                                    Else
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    End If
                                End If
                                If StrBMData = "EmailError_IT_Contact" Then
                                    System.IO.File.Delete(txtfile)
                                    Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                                    Exit Function
                                End If
                            Next
                        End If
                    End If
                    oRead = IO.File.OpenText(txtfile)
                    strRep = oRead.ReadToEnd
                    oRead.Close()

                    For i = 1 To cData.Count
                        If OpToEmail = String.Empty Then
                            OpToEmail = strRep.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                        Else
                            OpToEmail = OpToEmail.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                        End If
                    Next
                    'Dim subj As String = "Global Visas Case Update"
                    Dim Signature As String = String.Empty
                    If blnSignature = False Then
                        Signature = vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                    Else

                        Signature = "PERMITS AND VISAS TEAM" & vbCrLf & vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                    End If
                    OpToEmail = OpToEmail & vbCrLf & Signature
                Else
                    oRead = IO.File.OpenText(txtfile)

                    strRep = oRead.ReadToEnd
                    oRead.Close()
                    If OpToEmail = String.Empty Then
                        OpToEmail = strRep
                    End If
                    Dim Signature As String = String.Empty
                    If blnSignature = False Then
                        Signature = vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                    Else

                        Signature = "PERMITS AND VISAS TEAM" & vbCrLf & vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                    End If
                    OpToEmail = OpToEmail & vbCrLf & Signature
                End If
                
                Dim Success As Integer = Letters.GblFunc_Send_MailEngine(EmailTo, EmailFrom, myTemplate.SubjectLine.ToString.Trim, OpToEmail)

                Dim Msg As New StringBuilder
                With Msg
                    .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

                    .Append(vbCrLf & vbCrLf & "Sent via mail engine")
                    .Append(vbCrLf & "Subject: " & myTemplate.SubjectLine.ToString.Trim)
                    '.Append(vbCrLf & "Attachments : " & attachnames.ToString)
                    .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & OpToEmail)
                End With

                'code send mail through server
                Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
                Dim Smtp_Server As New SmtpClient
                Dim e_mail As New MailMessage()
                Smtp_Server.UseDefaultCredentials = False
                Dim userName As String = CType(configurationAppSettings.GetValue("emailserverusername", GetType(System.String)), String)
                Dim password As String = CType(configurationAppSettings.GetValue("emailserverpassword", GetType(System.String)), String)
                Dim test As Singleton = Singleton.Instance
                Smtp_Server.Credentials = New Net.NetworkCredential(test.CurrentConEmail, test.CurrentConEmailPassword)
                'Smtp_Server.Port = 25
                'Smtp_Server.EnableSsl = True
                Dim smtpserver As String = CType(configurationAppSettings.GetValue("smtpserver", GetType(System.String)), String)
                Smtp_Server.Host = smtpserver

                e_mail = New MailMessage()
                e_mail.From = New MailAddress(EmailFrom)
                e_mail.To.Add(EmailTo)
                ' e_mail.Bcc = EMAILBCCSTRING
                e_mail.Subject = GlobalCRM.subj
                'e_mail.IsBodyHtml = False
                e_mail.Body = EMAILBODYSTRING
                Smtp_Server.Send(e_mail)


                tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
                If Success = 1 Then
                    MsgBox("Email has been sent to the client successfully.", MsgBoxStyle.Information, "Success")
                End If
                GlobalCRM.LocalAttachments.Attachments.Clear()
                System.IO.File.Delete(txtfile)
                CommonFuncs.ForceDeleteTempDir()
                Return True
            End If
            'End If
        Catch ex As Exception
            'CommonFunc.ErrorHandler(ex, "CommonFunc.SendEmailToClient", True)
            Return False
        End Try
    End Function
    Public Shared Function GetEmailTemplate(ByVal TemplateId As Integer) As EmailTemplateInfo
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As New StringBuilder

        Dim myData As DataTable

        Dim myInfo As New EmailTemplateInfo
        Try


            With SQL
                .Append("Select EMailTemplate_Name, EMailTemplate_SubjectLine, EMailTemplate_Body from D_EMailTemplates")
                .Append(" Where EMailTemplate_ID = " & TemplateId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    With myInfo
                        .TemplateName = myData.Rows(0).Item("emailtemplate_name")
                        .SubjectLine = myData.Rows(0).Item("emailtemplate_subjectline")
                        .Body = myData.Rows(0).Item("emailtemplate_body")
                    End With

                End If

            End If

            myData.Dispose()

            Return myInfo
        Catch ex As Exception
            Return Nothing
        Finally
            myInfo = Nothing
            SQL = Nothing
            myData = Nothing
            db = Nothing

        End Try
    End Function
 
    Public Shared Function CSec(ByVal AccessItem As Integer) As Integer

        'Quick way to check security access

        CSec = Security.CheckSecurityAccess(AccessItem)

    End Function

    Public Shared Function CompleteTasksForCase_StageNavigation(ByVal caseid As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As StringBuilder
        Try


            SQL = New StringBuilder

            Dim dt As DataTable
            SQL.Append("select event_id from d_event where event_category = 2 and Event_LinkID = " & caseid & " and event_completedby is null")
            dt = DB.GetSQLDataTable(SQL.ToString)
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    Dim EventIds As String = CommonFuncs.ManipulateDT(dt)

                    Dim Msg As String = String.Empty  '= "Auto Close" & vbCrLf & "---------------------------------------------------------" & vbCrLf & "Completion message" & vbCrLf & vbCrLf & "The task is completed as the case has been navigated to next stage"
                    SQL = New StringBuilder
                    SQL.Append("UPDATE D_Event  SET Event_Category = 3, Event_CompletedBy = '" & 80 & "', Event_DTCompleted = getdate()  WHERE Event_ID in(" & EventIds & ")")

                    If DB.ExecuteSQL(SQL.ToString) = True Then
                        'add event that all the tasks are closed fo the case
                        'TasksEvents.GblFunc_Add_Event("Tasks Completed", "All the previous open tasks associated with this case has been closed now.", TasksEvents.EventStatus.SingleEvent, caseid, CommonSQL.EventTypes.EventCase)
                    End If
                End If
            End If
        Catch ex As Exception
        Finally
            DB = Nothing

        End Try
    End Function

    Public Shared Sub SendEmailToClient1(ByVal mycaseInfo As Cases.CaseInfo, ByVal mystageInfo As Cases.CaseStageLookupItem, ByVal myPerson As People.PersonInfo, Optional ByVal blnAlternative As Boolean = False)
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Try
            Dim blnHidePopup As Boolean = False
            Dim sqlbldr As New StringBuilder
            Dim EmailFrom As String
            Dim strTemp As String
            Dim Attach As GlobalCRM.LocalAttachments.LocalAttachment
            Dim DocStoreId As Long
            Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
            Dim TemplateID As Integer
            Dim attachnames As StringBuilder
            'Select - case for appropriate case advancement
            Dim blnSignature As Boolean = True
            'frmEmailCaseNavigation.btnCancel.Visible = True
            Dim blnToLoginArea As Boolean = False
            Select Case mycaseInfo.CaseStatusId
                'Case 100
                'TemplateID = 388
                Case 40


                    TemplateID = getCustomizationdata("Initiatecase")

                    'TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage_Initiate", GetType(System.Int16)), Integer)
                    sqlbldr.Append("SELECT Con_Email FROM dbo.L_Consultant WHERE Con_ID = " & getCustomizationdata("ConID" & GlobalCRM.CurrentBranchId))
                    EmailFrom = db.GetSQLScalar(sqlbldr.ToString)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If

                    Dim salescon As Integer = db.GetSQLScalar("select case_consaleid from d_case where case_id = " & GlobalCRM.CurrentCaseID)
                    If mystageInfo.StageNumber = 100 And blnAlternative = False Then
                        tsk.GblFunc_Add_Event("Allocate", "Please allocate the case to a CSM.", EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , salescon, DateTime.Now.AddDays(1))
                    End If
                Case 100
                    If blnAlternative = False Then
                        Dim CaseCoordinator As Integer = db.GetSQLScalar("select case_coordinatorid from d_case where case_id = " & GlobalCRM.CurrentCaseID)
                        If CaseCoordinator > 0 Then
                            tsk.GblFunc_Add_Event("Consultation", "Please contact the client and schedule consultation", EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , CaseCoordinator, DateTime.Now.AddDays(4))
                        End If
                    End If
                Case 700
                    'TemplateID = 388 'CSM Intro Letter
                    If blnAlternative = True Then
                        TemplateID = 0
                        Exit Select
                    End If
                    'If MsgBox("Is the case going to have a case co-ordinator?", MsgBoxStyle.YesNo, "Case co-ordinator?") = MsgBoxResult.Yes Then
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage3", GetType(System.Int16)), Integer)
                    'sqlbldr.Append("Select L_Consultant_super.Con_email as SuperName From  L_Consultant LEFT OUTER JOIN L_OISC ON L_Consultant.Con_OISC = L_OISC.OISC_ID LEFT OUTER JOIN L_Consultant L_Consultant_Super ON L_Consultant.Con_SupervisorID = L_Consultant_Super.Con_ID Where L_Consultant.Con_ID = " & mycaseInfo.CaseWorkerId)
                    sqlbldr.Append("SELECT Con_Email FROM dbo.L_Consultant WHERE Con_ID = " & getCustomizationdata("ConID" & GlobalCRM.CurrentBranchId))
                    EmailFrom = db.GetSQLScalar(sqlbldr.ToString)

                    'Else
                    '    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage_SkipCC", GetType(System.Int16)), Integer)
                    '    sqlbldr.Append("SELECT Con_Email FROM dbo.L_Consultant WHERE Con_ID = " & CType(configurationAppSettings.GetValue("ConIDHeadOffice", GetType(System.Int16)), Integer))
                    '    EmailFrom = DB.GetSQLScalar(sqlbldr.ToString)
                    'End If
                    blnToLoginArea = True
                Case 110
                    'TemplateID = 389 'CC Intro Letter
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage4", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    If blnAlternative = False Then
                        Dim CaseCoordinator As Integer = db.GetSQLScalar("select case_coordinatorid from d_case where case_id = " & GlobalCRM.CurrentCaseID)
                        Dim CSM As Integer = db.GetSQLScalar("select case_concwid from d_case where case_id = " & GlobalCRM.CurrentCaseID)
                        Dim casedtInitiated As Date = db.GetSQLScalar("select case_dtsold from d_case where case_id = " & GlobalCRM.CurrentCaseID)

                        If CaseCoordinator > 0 Then
                            tsk.GblFunc_Add_Event("Outstanding", "Please contact the client for outstanding balance", EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , CaseCoordinator, casedtInitiated.AddDays(25))
                            tsk.GblFunc_Add_Event("Close", "Close the case if no response from client", EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , CaseCoordinator, casedtInitiated.AddMonths(3))
                        End If
                        If CSM > 0 Then
                            tsk.GblFunc_Add_Event("Outstanding", "Please contact the client for outstanding balance", EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , CSM, casedtInitiated.AddDays(25))
                            tsk.GblFunc_Add_Event("Close", "Close the case if no response from client", EventStatus.Task, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , CaseCoordinator, casedtInitiated.AddMonths(3))
                        End If
                    End If
                    blnToLoginArea = True
                Case 710
                    'TemplateID = 390 'All docs received
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage5", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 720
                    'TemplateID = 391 'Case with CSM
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage6", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 730
                    'TemplateID = 392 'Application Sent for approval
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage7", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 130
                    'TemplateID = 393 'Application Submitted
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage8", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                        'TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_OverSeas", GetType(System.Int16)), Integer)
                        'blnSignature = False
                    End If
                    blnToLoginArea = True
                Case 151
                    'TemplateID = 394
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage9", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        If CType(configurationAppSettings.GetValue("GBR-AUS_ProductTypeID", GetType(System.Int16)), Integer) = mycaseInfo.ProductTypeId Or CType(configurationAppSettings.GetValue("GBR-USA_ProductTypeID", GetType(System.Int16)), Integer) = mycaseInfo.ProductTypeId Or CType(configurationAppSettings.GetValue("GBR-CAN_ProductTypeID", GetType(System.Int16)), Integer) = mycaseInfo.ProductTypeId Then
                            TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_OverSeas", GetType(System.Int16)), Integer)
                            blnSignature = False
                        Else
                            TemplateID = 0
                        End If
                    End If
                    blnToLoginArea = True
                Case 179
                    'TemplateID = 396
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage10", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 740
                    'TemplateID = 397
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage5", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 750
                    'TemplateID = 396
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage10", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 760
                    'TemplateID = 399
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage13", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 770
                    'TemplateID = 399
                    'Select Case mystageInfo.StageNumber
                    '    Case 170, 1010
                    '        TemplateID = 0
                    '    Case Else
                    '        TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage14", GetType(System.Int16)), Integer)
                    'End Select
                    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage14", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
                Case 780
                    'TemplateID = 399
                    'TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_ToCaseStage14", GetType(System.Int16)), Integer)
                    'Case 130
                    '    TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_OverSeas", GetType(System.Int16)), Integer)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                    blnToLoginArea = True
            End Select
            Select Case mystageInfo.StageNumber
                Case 1010, 1030 'These are case cancel stage numbers
                    If getCustomizationdata("GBR-WWV_ProductTypeID") = mycaseInfo.ProductTypeId Then
                        Exit Sub
                    Else
                        'frmEmailCaseNavigation.btnCancel.Visible = False
                        'TemplateID = 442
                        TemplateID = 594
                        blnHidePopup = True
                        sqlbldr.Append("SELECT Con_Email FROM dbo.L_Consultant WHERE Con_ID = " & getCustomizationdata("ConID" & GlobalCRM.CurrentBranchId))
                        EmailFrom = db.GetSQLScalar(sqlbldr.ToString)
                        If blnAlternative = True Then
                            TemplateID = 0
                        End If
                    End If
                Case 170
                    TemplateID = 0
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If
                Case 1000
                    'frmEmailCaseNavigation.btnCancel.Visible = False
                    'TemplateID = 442
                    TemplateID = 594
                    blnHidePopup = True
                    sqlbldr.Append("SELECT Con_Email FROM dbo.L_Consultant WHERE Con_ID = " & getCustomizationdata("ConID" & GlobalCRM.CurrentBranchId))
                    EmailFrom = db.GetSQLScalar(sqlbldr.ToString)
                    If blnAlternative = True Then
                        TemplateID = 0
                    End If

            End Select
            If TemplateID <> 0 Then
                'Dim EmailFrom As String
                Dim EmailTo As String = String.Empty

                If EmailFrom = String.Empty Then
                    sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & GlobalCRM.CurrentConID)
                    EmailFrom = db.GetSQLScalar(sqlbldr.ToString)
                End If
                If EmailFrom Is Nothing Then
                    MsgBox("Your email id is not updated in the database." & vbCrLf & "Please contact IT", MsgBoxStyle.Critical, "Email Not sent")
                    Exit Sub

                End If

                Dim DataType As Integer
                Dim DataID As Long
                If GlobalCRM.CurrentCompanyID = 0 Then
                    DataType = 1
                    DataID = mycaseInfo.CaseId
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & myPerson.PersonId)
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Exit Sub
                    End If
                Else
                    DataType = 3
                    DataID = GlobalCRM.CurrentCompanyID
                    'Fetch the company's contact and send email to associated ocntact and not the candidate
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("SELECT     dbo.D_EmailAddress.EMailAddress_Address FROM   dbo.D_Case INNER JOIN   dbo.D_EmailAddress ON dbo.D_Case.Case_ContactID = dbo.D_EmailAddress.EMailAddress_PersonID WHERE     (dbo.D_Case.Case_ID = " & GlobalCRM.CurrentCaseID & ")")
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Exit Sub
                    End If
                End If
                Dim dt As DataTable

                Dim myTemplate As CommonDataFunctions.EmailTemplateInfo
                myTemplate = CommonDataFunctions.GetEmailTemplate(TemplateID)
                '  Dim StrSQL As String

                Dim StrSQLBookmarks As String = String.Empty
                Dim intCount As Integer = 1
                Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
                Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

                Dim oWrite As System.IO.StreamWriter
                oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
                oWrite.Write(myTemplate.Body)
                oWrite.Close()

                Dim oRead As System.IO.StreamReader
                Dim LineIn As String
                Dim strNew() As String
                Dim strnew1() As String
                Dim i As Integer
                Dim c As New Collection
                Dim cData As Collection = Nothing
                Dim cIndex As New Collection
                Dim bln As Boolean
                oRead = IO.File.OpenText(txtfile)
                While oRead.Peek <> -1
                    LineIn = oRead.ReadLine()
                    strNew = Split(LineIn, "[")
                    For i = 0 To strNew.Length - 1
                        strnew1 = Split(strNew(i).ToString, "]")
                        If strnew1.Length > 1 Then
                            'StrSQLBookmarks = strnew1(0).ToString
                            StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
                            If cIndex.Contains(strnew1(0).ToString) = False Then
                                cIndex.Add(i, strnew1(0).ToString)
                                bln = True
                            End If
                            If bln Then
                                c.Add(strnew1(0).ToString)
                            End If
                            bln = False
                        End If
                    Next
                End While
                oRead.Close()

                StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
                StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

                Dim sql1 As New StringBuilder
                With sql1
                    .Append("SELECT BookMark_Text as BMName")
                    .Append(" FROM I_SL_Bookmarks")
                    .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
                    '.Append(" AND Bookmark_VersionID <= " & 1)
                    .Append(" GROUP BY BookMark_Text")
                End With
                dt = db.GetSQLDataTable(sql1.ToString)
                If Not dt Is Nothing Then
                    If dt.Rows.Count > 0 Then
                        Dim StrBMData As String = String.Empty
                        cData = New Collection
                        For i = 0 To dt.Rows.Count - 1
                            ' So we can monitor value
                            strTemp = dt.Rows(i).Item("BMName")
                            If dt.Rows(i).Item("BMName") <> "" Then
                                If GlobalCRM.CurrentCompanyID <> 0 Then
                                    If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    Else
                                        DataType = 1
                                        DataID = mycaseInfo.CaseId
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    End If
                                Else
                                    StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                End If
                            End If
                            If StrBMData = "EmailError_IT_Contact" Then
                                System.IO.File.Delete(txtfile)
                                Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                                Exit Sub
                            End If
                        Next
                    End If
                End If
                oRead = IO.File.OpenText(txtfile)
                Dim strRep As String = oRead.ReadToEnd
                oRead.Close()
                Dim OpToEmail As String = String.Empty
                For i = 1 To cData.Count
                    If OpToEmail = String.Empty Then
                        OpToEmail = strRep.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                    Else
                        OpToEmail = OpToEmail.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                    End If
                Next
                'Dim subj As String = "Global Visas Case Update"
                Dim Signature As String = String.Empty
                If blnSignature = False Then
                    Signature = vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                Else

                    Signature = "GLOBAL VISAS TEAM" & vbCrLf & vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                End If
                OpToEmail = OpToEmail & vbCrLf & Signature
                'EMAILBCCSTRING
                EMAILBODYSTRING = OpToEmail
                EMAILSUBJECTSTRING = GlobalCRM.subj
                EMAILFROMSTRING = EmailFrom
                EMAILTOSTRING = EmailTo

                'If TemplateID = 442 Then
                'If TemplateID = 594 Then
                '    frmEmailCaseNavigation.txtEmailBody.Enabled = False
                '    frmEmailCaseNavigation.txtSubject.Enabled = False
                'Else
                'frmEmailCaseNavigation.btnCancel.Visible = False
                'frmEmailCaseNavigation.txtEmailBody.Enabled = True
                'frmEmailCaseNavigation.txtSubject.Enabled = True
                'End If
                If blnHidePopup = False Then
                    'frmEmailCaseNavigation.ShowDialog()
                Else
                    'blnAmendDataBeforeEmail = False
                End If
               
                attachnames = New StringBuilder
                If GlobalCRM.LocalAttachments.Attachments.Count > 0 Then
                    'Add files to Docstore then pass Docstore ID to the mail engine
                    DocStoreId = Letters.GblFunc_Create_DocStore("Files E-Mailed", "Documents E-Mailed", GlobalCRM.CurrentCaseID, 1)
                    For Each Attach In GlobalCRM.LocalAttachments.Attachments

                        If Letters.GblFunc_Add_FileToStore(Attach.Filepath, Strings.Left(Attach.Name, 99), DocStoreId) = 0 Then
                            MsgBox("Couldn't find attachment file, send failed", MsgBoxStyle.Critical)
                            Exit Sub
                        End If

                        attachnames.Append(Attach.Filepath)
                        attachnames.Append(vbCrLf)
                    Next
                End If
                If blnToLoginArea = False Then
                    Dim Success As Integer = Letters.GblFunc_Send_MailEngine(EMAILTOSTRING, EMAILFROMSTRING, EMAILSUBJECTSTRING, EMAILBODYSTRING, , EMAILBCCSTRING, DocStoreId)

                    Dim Msg As New StringBuilder
                    With Msg
                        .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

                        .Append(vbCrLf & vbCrLf & "Sent via mail engine")
                        .Append(vbCrLf & "Subject: " & GlobalCRM.subj)
                        .Append(vbCrLf & "Attachments : " & attachnames.ToString)
                        .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & EMAILBODYSTRING)
                    End With
                    EMAILSUBJECTSTRING = String.Empty
                    EMAILBODYSTRING = String.Empty
                    EMAILBCCSTRING = String.Empty
                    tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
                    If Success = 1 Then
                        'MsgBox("Email has been sent to the client successfully.", MsgBoxStyle.Information, "Success")
                    End If
                    GlobalCRM.LocalAttachments.Attachments.Clear()
                    System.IO.File.Delete(txtfile)
                    cmn.ForceDeleteTempDir()
                Else
                    Dim candid As Long = 0
                    If GlobalCRM.CurrentCompanyID = 0 Then
                        candid = GlobalCRM.CurrentCandidateID
                    ElseIf GlobalCRM.CurrentCompanyID > 0 Then
                        If GlobalCRM.CurrentContactID > 0 Then
                            candid = GlobalCRM.CurrentContactID
                        Else
                            candid = GlobalCRM.CurrentCandidateID
                        End If
                    End If
                    'Login Area
                    If Letters.SendMailMessage(EMAILTOSTRING, GlobalCRM.CurrentConID, GlobalCRM.CurrentCaseID, EMAILSUBJECTSTRING, EMAILBODYSTRING, candid, EMAILBCCSTRING) = 0 Then
                        'Me.btnSend.Enabled = True
                        MsgBox("Error. Message could not be added to the S_SendEmail table.")
                        Exit Sub
                    Else
                        'Dim msgbody As StringBuilder

                        SendEmailToClient_ForLoginArea(GlobalCRM.CurrentCandidateID, EmailType.Candidate)

                        tsk.GblFunc_Add_Event("Send Email", "The email has been sent to the client and is viewable through login Area : " & vbCrLf & "Subject :" & EMAILSUBJECTSTRING & vbCrLf & "Body :" & vbCrLf & EMAILBODYSTRING, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
                        Try


                            GlobalCRM.LocalAttachments.Attachments.Clear()

                            'delete the draft
                            ' Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
                            GlobalCRM.LocalAttachments.Attachments.Clear()
                            System.IO.File.Delete(txtfile)
                            cmn.ForceDeleteTempDir()
                        Catch ex As Exception

                        End Try
                        '                End If
                    End If

                End If
            End If

        Catch ex As Exception
            ' CommonFunc.ErrorHandler(ex, "CommonFunc.SendEmailToClient", True)
        Finally
            db = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try
    End Sub

    Public Shared Function GetEmailTemplateID(ByVal FromID As Long, ByVal ToID As Long, ByVal Diagramid As Long) As Long
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append(" Select StageLink_EmailTemplate FROM         dbo.L_StageLinks")
                .Append(" WHERE     (StageLink_FromID = " & ToID & ") AND (StageLink_ToID = " & FromID & ") AND (StageLink_DiagramID = " & Diagramid & ")")
            End With
            Return db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
 
    Public Shared Function GetUnassignedEmailTemplateBody() As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append(" SELECT [EMailTemplate_Body]  FROM [Global].[dbo].[D_EmailTemplates] where EMailTemplate_Name = 'New Web Lead Assigned Email'")
            End With
            Return db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return 0
        Finally
            SQL = Nothing
            db = Nothing
        End Try
    End Function
    Public Shared Function SendEmailUnassignedInformationToClient(ByVal mycaseInfo As Cases.CaseInfo)
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Try
            Dim EmailFrom As String
            Dim EmailTo As String
            Dim strTemp As String
            Dim sqlbldr As New StringBuilder
            Dim DocStoreId As Long
            Dim attachnames As StringBuilder
            Dim TemplateBody As String
            TemplateBody = GetUnassignedEmailTemplateBody()

            If Not String.IsNullOrEmpty(TemplateBody) Then
                'EmailFrom
                If String.IsNullOrEmpty(EmailFrom) Then
                    sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & mycaseInfo.SalesConAssignedId)
                    EmailFrom = db.GetSQLScalar(sqlbldr.ToString)
                End If
                If EmailFrom Is Nothing Then
                    MsgBox("Your email id is not updated in the database." & vbCrLf & "Please contact IT", MsgBoxStyle.Critical, "Email Not sent")
                    Return False
                End If
                'emailTo
                Dim DataType As Integer
                Dim DataID As Long
                If GlobalCRM.CurrentCompanyID = 0 Then
                    DataType = 1
                    DataID = mycaseInfo.CaseId
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & mycaseInfo.CandidateId)
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Return False
                    End If
                Else
                    DataType = 3
                    DataID = GlobalCRM.CurrentCompanyID
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("SELECT     dbo.D_EmailAddress.EMailAddress_Address FROM   dbo.D_Case INNER JOIN   dbo.D_EmailAddress ON dbo.D_Case.Case_ContactID = dbo.D_EmailAddress.EMailAddress_PersonID WHERE     (dbo.D_Case.Case_ID = " & mycaseInfo.CaseId & ")")
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Exit Function
                    End If
                End If

                'fetching bookmarks and replacing them
                Dim dt As DataTable

                Dim StrSQLBookmarks As String = String.Empty
                Dim intCount As Integer = 1
                Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
                Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

                Dim oWrite As System.IO.StreamWriter
                oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
                oWrite.Write(TemplateBody)
                oWrite.Close()

                Dim oRead As System.IO.StreamReader
                Dim LineIn As String
                Dim strNew() As String
                Dim strnew1() As String
                Dim i As Integer
                Dim c As New Collection
                Dim cData As Collection
                Dim cIndex As New Collection
                Dim bln As Boolean
                oRead = IO.File.OpenText(txtfile)
                While oRead.Peek <> -1
                    LineIn = oRead.ReadLine()
                    strNew = Split(LineIn, "<")
                    For i = 0 To strNew.Length - 1
                        strnew1 = Split(strNew(i).ToString, ">")
                        If strnew1.Length > 1 Then
                            'StrSQLBookmarks = strnew1(0).ToString
                            StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
                            If cIndex.Contains(strnew1(0).ToString) = False Then
                                cIndex.Add(i, strnew1(0).ToString)
                                bln = True
                            End If
                            If bln Then
                                c.Add(strnew1(0).ToString)
                            End If
                            bln = False
                        End If
                    Next
                End While
                oRead.Close()

                StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
                StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

                Dim sql1 As New StringBuilder
                With sql1
                    .Append("SELECT BookMark_Text as BMName")
                    .Append(" FROM I_SL_Bookmarks")
                    .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
                    '.Append(" AND Bookmark_VersionID <= " & 1)
                    .Append(" GROUP BY BookMark_Text")
                End With
                dt = db.GetSQLDataTable(sql1.ToString)
                If Not dt Is Nothing Then
                    If dt.Rows.Count > 0 Then
                        Dim StrBMData As String = String.Empty
                        cData = New Collection
                        For i = 0 To dt.Rows.Count - 1
                            ' So we can monitor value
                            strTemp = dt.Rows(i).Item("BMName")
                            If dt.Rows(i).Item("BMName") <> "" Then
                                If GlobalCRM.CurrentCompanyID <> 0 Then
                                    If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    Else
                                        DataType = 1
                                        DataID = mycaseInfo.CaseId
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    End If
                                Else
                                    StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                End If
                            End If
                            If StrBMData = "EmailError_IT_Contact" Then
                                System.IO.File.Delete(txtfile)
                                Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                                Exit Function
                            End If
                        Next
                    End If
                End If
                oRead = IO.File.OpenText(txtfile)
                Dim strRep As String = oRead.ReadToEnd
                oRead.Close()
                'Adding Signature
                Dim Signature As String = String.Empty
                Signature = "PERMITS AND VISAS TEAM" & vbCrLf & vbCrLf & GblFunc_Get_Sigfile(mycaseInfo.SalesConAssignedId, False)

                Dim OpToEmail As String = String.Empty
                For i = 1 To cData.Count
                    If OpToEmail = String.Empty Then
                        OpToEmail = strRep.Replace("<" & c.Item(i) & ">", cData.Item(c.Item(i)))
                    Else
                        OpToEmail = OpToEmail.Replace("<" & c.Item(i) & ">", cData.Item(c.Item(i)))
                    End If
                Next
                OpToEmail = OpToEmail & vbCrLf & Signature
                'saving body ,subject, emailto and emailfrom
                EMAILBODYSTRING = OpToEmail
                EMAILSUBJECTSTRING = "Immigration Consultant Introduction"
                EMAILFROMSTRING = EmailFrom
                EMAILTOSTRING = EmailTo

                'Code for attachment
                attachnames = New StringBuilder
                If GlobalCRM.LocalAttachments.Attachments.Count > 0 Then
                    'Add files to Docstore then pass Docstore ID to the mail engine
                    DocStoreId = Letters.GblFunc_Create_DocStore("Files E-Mailed", "Documents E-Mailed", GlobalCRM.CurrentCaseID, 1)
                    For Each Attach In GlobalCRM.LocalAttachments.Attachments

                        If Letters.GblFunc_Add_FileToStore(Attach.Filepath, Strings.Left(Attach.Name, 99), DocStoreId) = 0 Then
                            MsgBox("Couldn't find attachment file, send failed", MsgBoxStyle.Critical)
                            Exit Function
                        End If

                        attachnames.Append(Attach.Filepath)
                        attachnames.Append(vbCrLf)
                    Next
                End If

                'saving email information
                Dim Msg As New StringBuilder
                With Msg
                    .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

                    .Append(vbCrLf & vbCrLf & "Sent via mail engine")
                    .Append(vbCrLf & "Subject: " & GlobalCRM.subj)
                    .Append(vbCrLf & "Attachments : " & attachnames.ToString)
                    .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & EMAILBODYSTRING)
                End With

                'code send mail through server
                Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
                Dim Smtp_Server As New SmtpClient
                Dim e_mail As New MailMessage()
                Smtp_Server.UseDefaultCredentials = False
                Dim userName As String = CType(configurationAppSettings.GetValue("emailserverusername", GetType(System.String)), String)
                Dim password As String = CType(configurationAppSettings.GetValue("emailserverpassword", GetType(System.String)), String)
                Dim test As Singleton = Singleton.Instance
                Smtp_Server.Credentials = New Net.NetworkCredential(test.CurrentConEmail, test.CurrentConEmailPassword)
                Smtp_Server.Port = 25
                'Smtp_Server.EnableSsl = True
                Dim smtpserver As String = CType(configurationAppSettings.GetValue("smtpserver", GetType(System.String)), String)
                Smtp_Server.Host = smtpserver

                e_mail = New MailMessage()
                e_mail.From = New MailAddress(EmailFrom)
                e_mail.To.Add(EmailTo)
                ' e_mail.Bcc = EMAILBCCSTRING
                e_mail.Subject = GlobalCRM.subj
                e_mail.IsBodyHtml = False
                e_mail.Body = EMAILBODYSTRING
                Smtp_Server.Send(e_mail)

                EMAILSUBJECTSTRING = String.Empty
                EMAILBODYSTRING = String.Empty
                EMAILBCCSTRING = String.Empty
                tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
            End If

        Catch ex As Exception

        End Try

    End Function
    Public Shared Function SendEmailToClient(ByVal mycaseInfo As Cases.CaseInfo, ByVal mystageInfo As Cases.CaseStageLookupItem, ByVal myPerson As People.PersonInfo, ByVal DiagramId As Long, Optional ByVal blnAlternative As Boolean = False) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Try
            Dim blnHidePopup As Boolean = False
            Dim sqlbldr As New StringBuilder
            Dim EmailFrom As String
            Dim strTemp As String
            Dim Attach As GlobalCRM.LocalAttachments.LocalAttachment
            Dim DocStoreId As Long
            ' Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
            Dim TemplateID As Integer
            Dim attachnames As StringBuilder
            'Select - case for appropriate case advancement
            Dim blnSignature As Boolean = True
            'frmEmailCaseNavigation.btnCancel.Visible = True
            Dim blnToLoginArea As Boolean = False

            TemplateID = GetEmailTemplateID(mystageInfo.StageNumber, mycaseInfo.CaseStatusId, DiagramId)
            If TemplateID > 0 Then

                'Dim EmailFrom As String
                Dim EmailTo As String = String.Empty

                If EmailFrom = String.Empty Then
                    sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & GlobalCRM.CurrentConID)
                    EmailFrom = db.GetSQLScalar(sqlbldr.ToString)
                End If
                If EmailFrom Is Nothing Then
                    MsgBox("Your email id is not updated in the database." & vbCrLf & "Please contact IT", MsgBoxStyle.Critical, "Email Not sent")
                    Return False
                End If

                Dim DataType As Integer
                Dim DataID As Long
                If GlobalCRM.CurrentCompanyID = 0 Then
                    DataType = 1
                    DataID = mycaseInfo.CaseId
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & myPerson.PersonId)
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Return False
                    End If
                Else
                    DataType = 3
                    DataID = GlobalCRM.CurrentCompanyID
                    sqlbldr = New StringBuilder
                    sqlbldr.Append("SELECT     dbo.D_EmailAddress.EMailAddress_Address FROM   dbo.D_Case INNER JOIN   dbo.D_EmailAddress ON dbo.D_Case.Case_ContactID = dbo.D_EmailAddress.EMailAddress_PersonID WHERE     (dbo.D_Case.Case_ID = " & GlobalCRM.CurrentCaseID & ")")
                    EmailTo = db.GetSQLScalar(sqlbldr.ToString)
                    If EmailTo Is Nothing Then
                        MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                        Exit Function
                    End If
                End If
                Dim dt As DataTable

                Dim myTemplate As CommonDataFunctions.EmailTemplateInfo
                myTemplate = CommonDataFunctions.GetEmailTemplate(TemplateID)

                Dim StrSQLBookmarks As String = String.Empty
                Dim intCount As Integer = 1
                Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
                Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

                Dim oWrite As System.IO.StreamWriter
                oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
                oWrite.Write(myTemplate.Body)
                oWrite.Close()

                Dim oRead As System.IO.StreamReader
                Dim LineIn As String
                Dim strNew() As String
                Dim strnew1() As String
                Dim i As Integer
                Dim c As New Collection
                Dim cData As Collection
                Dim cIndex As New Collection
                Dim bln As Boolean
                oRead = IO.File.OpenText(txtfile)
                While oRead.Peek <> -1
                    LineIn = oRead.ReadLine()
                    strNew = Split(LineIn, "<")
                    For i = 0 To strNew.Length - 1
                        strnew1 = Split(strNew(i).ToString, ">")
                        If strnew1.Length > 1 Then
                            'StrSQLBookmarks = strnew1(0).ToString
                            StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
                            If cIndex.Contains(strnew1(0).ToString) = False Then
                                cIndex.Add(i, strnew1(0).ToString)
                                bln = True
                            End If
                            If bln Then
                                c.Add(strnew1(0).ToString)
                            End If
                            bln = False
                        End If
                    Next
                End While
                oRead.Close()

                StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
                StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

                Dim sql1 As New StringBuilder
                With sql1
                    .Append("SELECT BookMark_Text as BMName")
                    .Append(" FROM I_SL_Bookmarks")
                    .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
                    '.Append(" AND Bookmark_VersionID <= " & 1)
                    .Append(" GROUP BY BookMark_Text")
                End With
                dt = db.GetSQLDataTable(sql1.ToString)
                If Not dt Is Nothing Then
                    If dt.Rows.Count > 0 Then
                        Dim StrBMData As String = String.Empty
                        cData = New Collection
                        For i = 0 To dt.Rows.Count - 1
                            ' So we can monitor value
                            strTemp = dt.Rows(i).Item("BMName")
                            If dt.Rows(i).Item("BMName") <> "" Then
                                If GlobalCRM.CurrentCompanyID <> 0 Then
                                    If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    Else
                                        DataType = 1
                                        DataID = mycaseInfo.CaseId
                                        StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                        cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                    End If
                                Else
                                    StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                End If
                            End If
                            If StrBMData = "EmailError_IT_Contact" Then
                                System.IO.File.Delete(txtfile)
                                Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                                Exit Function
                            End If
                        Next
                    End If
                End If
                oRead = IO.File.OpenText(txtfile)
                Dim strRep As String = oRead.ReadToEnd
                oRead.Close()
                Dim OpToEmail As String = String.Empty
                For i = 1 To cData.Count
                    If OpToEmail = String.Empty Then
                        OpToEmail = strRep.Replace("<" & c.Item(i) & ">", cData.Item(c.Item(i)))
                    Else
                        OpToEmail = OpToEmail.Replace("<" & c.Item(i) & ">", cData.Item(c.Item(i)))
                    End If
                Next
                'Dim subj As String = "Global Visas Case Update"
                Dim Signature As String = String.Empty
                If blnSignature = False Then
                    Signature = vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                Else

                    Signature = "PERMITS AND VISAS TEAM" & vbCrLf & vbCrLf & GblFunc_Get_Sigfile(GlobalCRM.CurrentConID, blnSignature)
                End If
                OpToEmail = OpToEmail & vbCrLf & Signature
                'EMAILBCCSTRING
                EMAILBODYSTRING = OpToEmail
                EMAILSUBJECTSTRING = myTemplate.SubjectLine
                EMAILFROMSTRING = EmailFrom
                EMAILTOSTRING = EmailTo

             

                attachnames = New StringBuilder
                If GlobalCRM.LocalAttachments.Attachments.Count > 0 Then
                    'Add files to Docstore then pass Docstore ID to the mail engine
                    DocStoreId = Letters.GblFunc_Create_DocStore("Files E-Mailed", "Documents E-Mailed", GlobalCRM.CurrentCaseID, 1)
                    For Each Attach In GlobalCRM.LocalAttachments.Attachments

                        If Letters.GblFunc_Add_FileToStore(Attach.Filepath, Strings.Left(Attach.Name, 99), DocStoreId) = 0 Then
                            MsgBox("Couldn't find attachment file, send failed", MsgBoxStyle.Critical)
                            Exit Function
                        End If

                        attachnames.Append(Attach.Filepath)
                        attachnames.Append(vbCrLf)
                    Next
                End If
                If blnToLoginArea = False Then
                    Dim Success As Integer = Letters.GblFunc_Send_MailEngine(EMAILTOSTRING, EMAILFROMSTRING, EMAILSUBJECTSTRING, EMAILBODYSTRING, , EMAILBCCSTRING, DocStoreId)

                    Dim Msg As New StringBuilder
                    With Msg
                        .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

                        .Append(vbCrLf & vbCrLf & "Sent via mail engine")
                        .Append(vbCrLf & "Subject: " & GlobalCRM.subj)
                        .Append(vbCrLf & "Attachments : " & attachnames.ToString)
                        .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & EMAILBODYSTRING)
                    End With
                    'code send mail through server
                    Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
                    Dim Smtp_Server As New SmtpClient
                    Dim e_mail As New MailMessage()
                    Smtp_Server.UseDefaultCredentials = False
                    Dim test As Singleton = Singleton.Instance
                    'Dim userName As String = CType(configurationAppSettings.GetValue("emailserverusername", GetType(System.String)), String)
                    'Dim password As String = CType(configurationAppSettings.GetValue("emailserverpassword", GetType(System.String)), String)
                    Smtp_Server.Credentials = New Net.NetworkCredential(test.CurrentConEmail, test.CurrentConEmailPassword)
                    'Smtp_Server.Port = 25
                    'Smtp_Server.EnableSsl = True
                    Dim smtpserver As String = CType(configurationAppSettings.GetValue("smtpserver", GetType(System.String)), String)
                    Smtp_Server.Host = smtpserver

                    e_mail = New MailMessage()
                    e_mail.From = New MailAddress(EmailFrom)
                    e_mail.To.Add(EmailTo)
                    ' e_mail.Bcc = EMAILBCCSTRING
                    e_mail.Subject = GlobalCRM.subj
                    'e_mail.IsBodyHtml = False
                    e_mail.Body = EMAILBODYSTRING
                    For Each attachment As GlobalCRM.LocalAttachments.LocalAttachment In GlobalCRM.LocalAttachments.Attachments
                        e_mail.Attachments.Add(New Net.Mail.Attachment(attachment.Filepath))
                    Next
                    Smtp_Server.Send(e_mail)

                    EMAILSUBJECTSTRING = String.Empty
                    EMAILBODYSTRING = String.Empty
                    EMAILBCCSTRING = String.Empty
                    tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
                    If Success = 1 Then
                        MsgBox("Email has been sent to the client successfully.", MsgBoxStyle.Information, "Success")
                    End If
                    GlobalCRM.LocalAttachments.Attachments.Clear()
                    System.IO.File.Delete(txtfile)
                    Try
                        cmn.ForceDeleteTempDir()
                    Catch exee As Exception
                    End Try
                    Return True
                Else
                    Dim candid As Long = 0
                    If GlobalCRM.CurrentCompanyID = 0 Then
                        candid = GlobalCRM.CurrentCandidateID
                    ElseIf GlobalCRM.CurrentCompanyID > 0 Then
                        If GlobalCRM.CurrentContactID > 0 Then
                            candid = GlobalCRM.CurrentContactID
                        Else
                            candid = GlobalCRM.CurrentCandidateID
                        End If
                    End If
                    'Login Area
                    If Letters.SendMailMessage(EMAILTOSTRING, GlobalCRM.CurrentConID, GlobalCRM.CurrentCaseID, EMAILSUBJECTSTRING, EMAILBODYSTRING, candid, EMAILBCCSTRING) = 0 Then
                        'Me.btnSend.Enabled = True
                        MsgBox("Error. Message could not be added to the S_SendEmail table.")
                        Exit Function
                    Else
                        'Dim msgbody As StringBuilder

                        SendEmailToClient_ForLoginArea(GlobalCRM.CurrentCandidateID, EmailType.Candidate)

                        tsk.GblFunc_Add_Event("Send Email", "The email has been sent to the client and is viewable through login Area : " & vbCrLf & "Subject :" & EMAILSUBJECTSTRING & vbCrLf & "Body :" & vbCrLf & EMAILBODYSTRING, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
                        Try


                            GlobalCRM.LocalAttachments.Attachments.Clear()

                            'delete the draft
                            ' Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
                            GlobalCRM.LocalAttachments.Attachments.Clear()
                            System.IO.File.Delete(txtfile)
                            cmn.ForceDeleteTempDir()
                            Return True
                        Catch ex As Exception
                            Return False
                        End Try
                        '                End If
                    End If

                End If
            End If

        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "CommonFunc.SendEmailToClient", True)
            Return False
        Finally
            db = Nothing
            tsk = Nothing
            cmn = Nothing
        End Try
    End Function
    Public Shared Function Gblfunc_Add_CaseAudit(ByVal CaseID As Long, ByVal AuditType As Integer, ByVal ValueFrom As Long, ByVal ValueTo As Long, ByVal ConsultantId As Integer) As Integer

        'Audit Types

        ' 0 = not valid
        ' 1 = Product
        ' 2 = Sales Con
        ' 3 = Caseworker
        ' 4 = Candidate
        ' 5 = Contact
        ' 6 = Status
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("Insert into D_CaseAudit")
                .Append(" (CaseAudit_EnteredBy, CaseAudit_CaseID, CaseAudit_AuditType, CaseAudit_IDOld, CaseAudit_IDNew)")
                .Append(" Values(")
                .Append(ConsultantId & "," & CaseID & "," & AuditType & ",")
                .Append(ValueFrom & ", " & ValueTo & ")")
            End With

            Return db.ExecuteSQL(SQL.ToString)


        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            SQL = Nothing
        End Try

    End Function
    'Public Shared Function xMsgBox(ByVal sPrompt As String, ByVal sTitle As String, _
    ' ByVal lFlagType As FlagType, ByVal lDefaultButton As DefaultButton, _
    ' Optional ByVal lLeft As Object = 0, Optional ByVal lTop As Object = 0, _
    ' Optional ByVal sText1 As Object = Nothing, Optional ByVal sText2 As Object = Nothing, _
    ' Optional ByVal sText3 As Object = Nothing, Optional ByVal sIconPath As Object = Nothing, _
    ' Optional ByVal hwndThreadOwner As Long = 0) As Integer

    '    Dim hInstance As Long
    '    Dim hThreadId As Long
    '    Dim hWndOwner As Long
    '    Dim lButtons As Long
    '    'Dim lWidth As Long
    '    'Dim lHeight As Long
    '    Dim RetVal As Integer

    '    'Setup the MsgBox parameters
    '    strPrompt = sPrompt
    '    StrTitle = sTitle
    '    mLeft = CLng(lLeft)
    '    mTop = CLng(lTop)

    '    'Work out which buttons to display, and what text to put in them.
    '    If (Not IsNothing(sText1)) And (Not IsNothing(sText2)) _
    '            And (Not IsNothing(sText3)) Then
    '        strYes = sText1 : strNo = sText2 : strCancel = sText3
    '        lButtons = vbYesNoCancel + lFlagType + lDefaultButton
    '    ElseIf (Not IsNothing(sText1)) And (Not IsNothing(sText2)) Then
    '        strYes = sText1 : strNo = sText2
    '        lButtons = vbYesNo + lFlagType + lDefaultButton
    '    ElseIf Not IsNothing(sText1) Then
    '        strYes = sText1
    '        lButtons = vbYes + lFlagType + lDefaultButton
    '    End If

    '    'Check for an icon
    '    sIcon = string.empty
    '    If Not IsNothing(sIconPath) Then
    '        If Dir(sIconPath, vbNormal) <> "" Then sIcon = sIconPath
    '    End If

    '    'Setup the hook.
    '    hInstance = GetWindowLong(CLng(hwndThreadOwner), GWL_HINSTANCE)
    '    hThreadId = GetCurrentThreadId()
    '    hWndOwner = GetDesktopWindow()

    '    'Setup the MSGBOX_HOOK_PARAMS values.
    '    'By specifying a Windows hook as one of the params, we can intercept messages
    '    'sent by Windows and thereby manipulate the dialog.
    '    With MSGHOOK
    '        .hWndOwner = hWndOwner
    '        .hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, hInstance, hThreadId)
    '    End With

    '    'Call the MessageBox API and return the value as the result of the function. The
    '    'length of the Prompt & Title strings assures the messagebox is wide enough for
    '    'the message that will be set in the hook.
    '    RetVal = MessageBox(hWndOwner, sPrompt, sTitle, lButtons)

    '    Select Case RetVal

    '        Case 6
    '            xMsgBox = 1

    '        Case 7
    '            xMsgBox = 2

    '        Case 2
    '            xMsgBox = 3

    '        Case Else
    '            xMsgBox = 0

    '    End Select


    'End Function
    Public Shared Function GetEventMessage(ByVal EventID As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder

            With SQL
                .Append("select event_message from d_event where event_id = " & EventID)
                Return db.GetSQLScalar(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            SQL = Nothing
            db = Nothing
        End Try




    End Function
    Public Shared Function AddExtraRowToDataTable(ByVal dtbMyTable As DataTable, ByVal intValue As Integer, Optional ByVal strText As String = "All") As DataTable
        ' use to add new row to an existing datatable, useful for having a select all option at top of a drop down list
        Try


            If Not dtbMyTable Is Nothing Then

                Dim dtrRow As DataRow
                dtrRow = dtbMyTable.NewRow

                dtrRow.Item(0) = intValue
                dtrRow.Item(1) = strText

                ' add at top of table
                dtbMyTable.Rows.InsertAt(dtrRow, 0)

                Return dtbMyTable
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    'Public Shared Sub SendEmailToClientForWebLeads(ByVal mycaseInfo As Cases.CaseInfo, ByVal myPerson As People.PersonInfo, Optional ByVal blnWWV As Boolean = False)

    '    Dim db As New Global_Visas_Data_Layer.DataAccess
    '    Try


    '        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
    '        Dim TemplateID As Long = CType(configurationAppSettings.GetValue("EmailTemplate_AllocateWebLeads", GetType(System.Int16)), Integer)
    '        'Dim TemplateID As Long = 409
    '        If blnWWV = True Then
    '            TemplateID = CType(configurationAppSettings.GetValue("EmailTemplate_WWVEmail", GetType(System.Int16)), Integer)
    '        End If

    '        If TemplateID <> 0 Then
    '            Dim EmailFrom As String
    '            Dim EmailTo As String
    '            Dim sqlbldr As New StringBuilder



    '            Dim DataType As Integer
    '            Dim DataID As Long
    '            If GlobalCRM.CurrentCompanyID = 0 Then
    '                DataType = 1
    '                DataID = mycaseInfo.CaseId
    '                sqlbldr = New StringBuilder
    '                sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & myPerson.PersonId)
    '                EmailTo = DB.GetSQLScalar(sqlbldr.ToString)
    '                If EmailTo Is Nothing Then
    '                    'MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
    '                    Exit Sub
    '                End If
    '                If EmailTo.Trim <> "" Then
    '                    If CommonFuncs.isEmailValid(EmailTo) = False Then
    '                        Exit Sub
    '                    End If

    '                End If
    '            Else
    '                DataType = 3
    '                DataID = GlobalCRM.CurrentCompanyID
    '                'Fetch the company's contact and send email to associated ocntact and not the candidate
    '                sqlbldr = New StringBuilder
    '                sqlbldr.Append("select EmailAddress_Address from D_EmailAddress where EmailAddress_personID in (select case_contactid from d_case where case_candidateid = " & myPerson.PersonId & ")")
    '                EmailTo = DB.GetSQLScalar(sqlbldr.ToString)
    '                If EmailTo Is Nothing Then
    '                    'MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
    '                    Exit Sub
    '                End If
    '                If CommonFuncs.isEmailValid(EmailTo) = False Then
    '                    Exit Sub
    '                End If
    '            End If
    '            Dim dt As DataTable

    '            Dim myTemplate As CommonDataFunctions.EmailTemplateInfo
    '            myTemplate = CommonDataFunctions.GetEmailTemplate(TemplateID)
    '            Dim StrSQL As String

    '            Dim StrSQLBookmarks As String = string.empty
    '            Dim intCount As Integer = 1
    '            Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
    '            Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

    '            Dim oWrite As System.IO.StreamWriter
    '            oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
    '            oWrite.Write(myTemplate.Body)
    '            oWrite.Close()

    '            Dim oRead As System.IO.StreamReader
    '            Dim LineIn As String
    '            Dim strNew() As String
    '            Dim strnew1() As String
    '            Dim i As Integer
    '            Dim c As New Collection
    '            Dim cData As Collection
    '            Dim cIndex As New Collection
    '            Dim bln As Boolean
    '            oRead = IO.File.OpenText(txtfile)
    '            While oRead.Peek <> -1
    '                LineIn = oRead.ReadLine()
    '                strNew = Split(LineIn, "[")
    '                For i = 0 To strNew.Length - 1
    '                    strnew1 = Split(strNew(i).ToString, "]")
    '                    If strnew1.Length > 1 Then
    '                        'StrSQLBookmarks = strnew1(0).ToString
    '                        StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
    '                        If cIndex.Contains(strnew1(0).ToString) = False Then
    '                            cIndex.Add(i, strnew1(0).ToString)
    '                            bln = True
    '                        End If
    '                        If bln Then
    '                            c.Add(strnew1(0).ToString)
    '                        End If
    '                        bln = False
    '                    End If
    '                Next
    '            End While
    '            oRead.Close()

    '            StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
    '            StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

    '            Dim sql1 As New StringBuilder
    '            With sql1
    '                .Append("SELECT BookMark_Text as BMName")
    '                .Append(" FROM I_SL_Bookmarks")
    '                .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
    '                '.Append(" AND Bookmark_VersionID <= " & 1)
    '                .Append(" GROUP BY BookMark_Text")
    '            End With
    '            dt = DB.GetSQLDataTable(sql1.ToString)
    '            If Not dt Is Nothing Then
    '                If dt.Rows.Count > 0 Then
    '                    Dim StrBMData As String = string.empty
    '                    cData = New Collection
    '                    For i = 0 To dt.Rows.Count - 1
    '                        If dt.Rows(i).Item("BMName") <> "" Then
    '                            If GlobalCRM.CurrentCompanyID <> 0 Then
    '                                If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
    '                                    StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
    '                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
    '                                Else
    '                                    DataType = 1
    '                                    DataID = mycaseInfo.CaseId
    '                                    StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
    '                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
    '                                End If
    '                            Else
    '                                If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
    '                                    StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)

    '                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
    '                                Else
    '                                    If dt.Rows(i).Item("BMName") = "ConCaseSuperEMail" Then
    '                                        StrBMData = DB.GetSQLScalar("select con_email from l_consultant where con_id = " & mycaseInfo.SalesConid)
    '                                    End If
    '                                    If dt.Rows(i).Item("BMName") = "ConCaseSuperDDI" Then
    '                                        StrBMData = DB.GetSQLScalar("select con_DDI from l_consultant where con_id = " & mycaseInfo.SalesConid)
    '                                    End If
    '                                    If dt.Rows(i).Item("BMName") = "ConCaseSuperName" Then
    '                                        StrBMData = DB.GetSQLScalar("select con_name from l_consultant where con_id = " & mycaseInfo.SalesConid)
    '                                    End If
    '                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))

    '                                    'Exit For
    '                                End If
    '                            End If
    '                        End If
    '                        If StrBMData = "EmailError_IT_Contact" Then
    '                            System.IO.File.Delete(txtfile)
    '                            Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
    '                            Exit Sub
    '                        End If
    '                    Next
    '                End If
    '            End If
    '            oRead = IO.File.OpenText(txtfile)
    '            Dim strRep As String = oRead.ReadToEnd
    '            oRead.Close()
    '            sqlbldr = New StringBuilder
    '            sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & mycaseInfo.SalesConid)
    '            EmailFrom = DB.GetSQLScalar(sqlbldr.ToString)

    '            If EmailFrom Is Nothing Then
    '                MsgBox("Your email id is not updated in the database." & vbCrLf & "Please contact IT", MsgBoxStyle.Critical, "Email Not sent")
    '                Exit Sub

    '            End If
    '            Dim OpToEmail As String
    '            For i = 1 To cData.Count
    '                If OpToEmail = string.empty Then
    '                    OpToEmail = strRep.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
    '                Else
    '                    OpToEmail = OpToEmail.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
    '                End If
    '            Next
    '            'Dim subj As String = "Global Visas Case Update"
    '            Dim Signature As String = CommonDataFunctions.GblFunc_Get_Sigfile(mycaseInfo.SalesConid)
    '            OpToEmail = OpToEmail & vbCrLf & Signature

    '            EMAILBODYSTRING = OpToEmail
    '            EMAILSUBJECTSTRING = GlobalCRM.subj
    '            EMAILFROMSTRING = EmailFrom

    '            EMAILTOSTRING = EmailTo
    '            'frmEmailCaseNavigation.btnBack.Enabled = False
    '            'frmEmailCaseNavigation.btnBack.Visible = False
    '            'frmEmailCaseNavigation.ShowDialog()
    '            'If blnAmendDataBeforeEmail Then
    '            '            Exit Sub
    '            'Else
    '            Dim Success As Integer = Letters.GblFunc_Send_MailEngine(EMAILTOSTRING, EMAILFROMSTRING, EMAILSUBJECTSTRING, EMAILBODYSTRING, , EMAILBCCSTRING)
    '            EMAILSUBJECTSTRING = string.empty
    '            EMAILBODYSTRING = string.empty
    '            EMAILBCCSTRING = string.empty
    '            Dim Msg As New StringBuilder
    '            With Msg
    '                .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

    '                .Append(vbCrLf & vbCrLf & "Sent via mail engine")
    '                .Append(vbCrLf & "Subject: " & GlobalCRM.subj)

    '                .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & OpToEmail)
    '            End With

    '            tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, mycaseInfo.CaseId, TasksEvents.EventTypes.EventCase, , , , , 1)
    '            If Success = 1 Then
    '                'MsgBox("Email has been sent to the client successfully." & vbCrLf & "Please ensure with your client whether the email address is correct", MsgBoxStyle.Information, "Success")
    '            End If
    '            System.IO.File.Delete(txtfile)
    '            Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
    '            'End If
    '        End If

    '        'Catch ex As Exception
    '        'Return False
    '        'End Try
    '    Catch ex As Exception
    '        CommonFuncs.ErrorHandler(ex, "CommonFunc.SendEmailToClientForWebLeads", True)
    '    Finally
    '        tsk = Nothing
    '        db = Nothing
    '    End Try
    'End Sub
    Public Shared Function CompanySizeList() As DataTable
        Dim DB As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
        
            With SQL
                .Append("select companysize_id,companysize_name + ' (' + companysize_desc + ')' as description from l_companysize order by companysize_order")
                Return DB.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetCurrencyCodeDesc(ByVal CurrentCaseID As Long)
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("SELECT dbo.L_Country.Country_CurrencyDesc")
                .Append(" FROM dbo.D_Case INNER JOIN")
                .Append(" dbo.L_Products ON dbo.D_Case.Case_ProductID = dbo.L_Products.Product_ID INNER JOIN")
                .Append(" dbo.L_ProductTypes ON dbo.L_Products.Product_Type = dbo.L_ProductTypes.ProductType_ID INNER JOIN")
                .Append(" dbo.L_Branch ON dbo.L_ProductTypes.ProductType_BranchID = dbo.L_Branch.Branch_ID INNER JOIN")
                .Append(" dbo.L_Country ON dbo.L_Branch.Branch_CountryID = dbo.L_Country.Country_ID")
                .Append(" where D_Case.Case_ID=" & CStr(CurrentCaseID))
            End With
            Return DB.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            DB = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function GetPaidAmountFromCaseid(ByVal caseid As Long) As Long
        Dim DB As New Global_Visas_Data_Layer.DataAccess
        Try
            Return DB.GetSQLScalar("SELECT paid FROM dbo.V_Case WHERE (Case_ID =" & GlobalCRM.CurrentCaseID & ")")
        Catch ex As Exception
            Return 0
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function GetCurrValue(ByVal strValue As String, Optional ByVal strCurrencyCode As String = Nothing) As Double
        Try

        
            Dim dblValue As Double
            Dim strRet As String
            If strCurrencyCode = Nothing Then
                strCurrencyCode = GetCurrencyCodeCharacter(GlobalCRM.CurrentCaseID)
            End If

            If InStr(strValue, strCurrencyCode) > 0 Then

                If Len(strValue) <= Len(strCurrencyCode) Or strValue = String.Empty Then
                    dblValue = 0
                Else
                    strRet = Mid(strValue, Len(strCurrencyCode) + 1)
                    dblValue = CDbl(strRet)
                End If
            Else
                If IsNumeric(strValue) = True Then
                    dblValue = CDbl(strValue)
                End If
            End If
            Return dblValue
        Catch ex As Exception

        End Try
    End Function
    Public Shared Function FormatCurr(ByVal strValue As String, Optional ByVal strCurrencyCode As String = Nothing) As String
        Try

        
            Dim dblValue As Double
            Dim strRet As String
            If strCurrencyCode = Nothing Then
                strCurrencyCode = GetCurrencyCodeCharacter(GlobalCRM.CurrentCaseID)
            End If
            If IsNumeric(strValue) = True Then
                dblValue = CDbl(strValue)
                strRet = strCurrencyCode & Format(dblValue, "###,###,###,##0.00")
            Else
                strRet = strValue
            End If
            Return strRet
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Shared Function AccountTypeList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As New StringBuilder
        Try

        
            With SQL
                .Append("select accounttype_id,accounttype_name from l_accounttypes order by accounttype_name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function CompanyStatusList() As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As New StringBuilder
        Try


            With SQL
                .Append("select companystatus_id,companystatus_name from l_companystatus order by companystatus_name")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function CorporateContactList(ByVal CompanyId As Long) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim SQL As New StringBuilder
        Try
            With SQL
                .Append("Select Person_ID, Person_Fname + ' ' + Person_LName as PersonName From D_Person INNER JOIN L_Roles ON D_Person.Person_CompanyRoleID = L_Roles.Role_ID WHERE Person_CompanyID = " & CompanyId & " AND Role_Employee in (1,2) ")
                Return db.GetSQLDataTable(.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Sub SendEmailForEditCase(ByVal mycaseInfo As Cases.CaseInfo, ByVal myPerson As Integer)
        Dim sqlbldr As New StringBuilder
        Dim EmailFrom, EmailTo As String
        Dim dt As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim tsk As New TasksEvents
        Dim cmn As New CommonFuncs
        Try


            Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
            Dim TemplateID As Integer = CType(configurationAppSettings.GetValue("EmailTemplate_ToAllocate", GetType(System.String)), String)
            'Dim DataType As Integer
            'Dim DataID As Long

            Dim myTemplate As CommonDataFunctions.EmailTemplateInfo
            myTemplate = CommonDataFunctions.GetEmailTemplate(TemplateID)
            'Dim StrSQL As String

            'If EmailFrom = string.empty Then
            '    MsgBox("The email ID of the case worker is not configured in the system. Please contact IT", MsgBoxStyle.Critical, "Error")
            '    Exit Sub
            'End If
            'sqlbldr = New StringBuilder
            'sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & myPerson)
            'EmailTo = DB.GetSQLScalar(sqlbldr.ToString)
            'If EmailTo Is Nothing Then
            '    MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
            '    Exit Sub
            'End If
            Dim DataType As Integer
            Dim DataID As Long
            If GlobalCRM.CurrentCompanyID = 0 Then
                DataType = 1
                DataID = mycaseInfo.CaseId
                sqlbldr = New StringBuilder
                sqlbldr.Append("Select EmailAddress_Address from D_EmailAddress where EmailAddress_personID = " & myPerson)
                EmailTo = DB.GetSQLScalar(sqlbldr.ToString)
                If EmailTo Is Nothing Then
                    MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                    Exit Sub
                End If
            Else
                DataType = 3
                DataID = GlobalCRM.CurrentCompanyID
                'Fetch the company's contact and send email to associated ocntact and not the candidate
                sqlbldr = New StringBuilder
                sqlbldr.Append("select EmailAddress_Address from D_EmailAddress where EmailAddress_personID in (select case_contactid from d_case where case_candidateid = " & myPerson & ")")
                EmailTo = DB.GetSQLScalar(sqlbldr.ToString)
                If EmailTo Is Nothing Then
                    MsgBox("The Email address of the client is not found in the system. Hence email has not been sent", MsgBoxStyle.OkOnly, "Email Not sent")
                    Exit Sub
                End If
            End If
            Dim StrSQLBookmarks As String = String.Empty
            Dim intCount As Integer = 1
            Dim txtfile As String = System.IO.Path.GetTempPath & "CRMTemp\sample.txt"
            Directory.CreateDirectory(System.IO.Path.GetTempPath & "CRMTemp")

            Dim oWrite As System.IO.StreamWriter
            oWrite = File.CreateText(System.IO.Path.GetTempPath & "CRMTemp\sample.txt")
            oWrite.Write(myTemplate.Body)
            oWrite.Close()

            Dim oRead As System.IO.StreamReader
            Dim LineIn As String
            Dim strNew() As String
            Dim strnew1() As String
            Dim i As Integer
            Dim c As New Collection
            Dim cData As Collection
            Dim cIndex As New Collection
            Dim bln As Boolean
            oRead = IO.File.OpenText(txtfile)
            While oRead.Peek <> -1
                LineIn = oRead.ReadLine()
                strNew = Split(LineIn, "[")
                For i = 0 To strNew.Length - 1
                    strnew1 = Split(strNew(i).ToString, "]")
                    If strnew1.Length > 1 Then
                        'StrSQLBookmarks = strnew1(0).ToString
                        StrSQLBookmarks = StrSQLBookmarks & "'" & strnew1(0).ToString & "',"
                        If cIndex.Contains(strnew1(0).ToString) = False Then
                            cIndex.Add(i, strnew1(0).ToString)
                            bln = True
                        End If
                        If bln Then
                            c.Add(strnew1(0).ToString)
                        End If
                        bln = False
                    End If
                Next
            End While
            oRead.Close()

            StrSQLBookmarks = Left(StrSQLBookmarks, (Len(StrSQLBookmarks) - 1))
            StrSQLBookmarks = "(" & StrSQLBookmarks & ")"

            Dim sql1 As New StringBuilder
            With sql1
                .Append("SELECT BookMark_Text as BMName")
                .Append(" FROM I_SL_Bookmarks")
                .Append(" WHERE Bookmark_Text in " & StrSQLBookmarks & "")
                '.Append(" AND Bookmark_VersionID <= " & 1)
                .Append(" GROUP BY BookMark_Text")
            End With
            dt = DB.GetSQLDataTable(sql1.ToString)
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    Dim StrBMData As String = String.Empty
                    cData = New Collection
                    For i = 0 To dt.Rows.Count - 1
                        If dt.Rows(i).Item("BMName") <> "" Then
                            If GlobalCRM.CurrentCompanyID <> 0 Then
                                If dt.Rows(i).Item("BMName") = "CandidateDearName" Then
                                    StrBMData = Letters.Gblfunc_Get_BookmarkData("ContactDearName", DataID, DataType)
                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                Else
                                    DataType = 1
                                    DataID = mycaseInfo.CaseId
                                    StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                    cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                                End If
                            Else
                                StrBMData = Letters.Gblfunc_Get_BookmarkData(dt.Rows(i).Item("BMName"), DataID, DataType)
                                cData.Add(StrBMData, dt.Rows(i).Item("BMName"))
                            End If
                        End If
                        If StrBMData = "EmailError_IT_Contact" Then
                            System.IO.File.Delete(txtfile)
                            Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp\")
                            Exit Sub
                        End If
                    Next
                End If
            End If
            oRead = IO.File.OpenText(txtfile)
            Dim strRep As String = oRead.ReadToEnd
            oRead.Close()
            Dim OpToEmail As String
            For i = 1 To cData.Count
                If OpToEmail = String.Empty Then
                    OpToEmail = strRep.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                Else
                    OpToEmail = OpToEmail.Replace("[" & c.Item(i) & "]", cData.Item(c.Item(i)))
                End If
            Next
            'Dim subj As String = "Global Visas Case Update"

            'Dim Signature As String
            Dim intSignatureBranchID As Integer = CommonDataFunctions.GetBranchID(mycaseInfo.CaseWorkerId)
            Dim Signature As String
            'Dim intSignatureOfficeID = CType(configurationAppSettings.GetValue("ConIDHeadOffice", GetType(System.Int16)), Integer)
            'Dim Signature As String = CType(configurationAppSettings.GetValue("ConIDHeadOffice", GetType(System.String)), String)
            Dim intSignatureConID As String = getCustomizationdata("ConID" & intSignatureBranchID)

            'Select Case intSignatureBranchID
            '    Case 1
            '        intSignatureConID = CType(configurationAppSettings.GetValue("ConIDHeadOffice", GetType(System.String)), String)
            '    Case 2
            '        intSignatureConID = CType(configurationAppSettings.GetValue("ConIDIndiaOffice", GetType(System.String)), String)
            '    Case 6
            '        'crm bug fix - for SA signatureerror 
            '        intSignatureConID = CType(configurationAppSettings.GetValue("ConIDSAOffice", GetType(System.String)), String)
            '    Case 4
            '        intSignatureConID = CType(configurationAppSettings.GetValue("ConIDCAOffice", GetType(System.String)), String)
            '    Case 3
            '        intSignatureConID = CType(configurationAppSettings.GetValue("ConIDUSAOffice", GetType(System.String)), String)
            'End Select
            sqlbldr = New StringBuilder
            sqlbldr.Append("Select Con_EMail from L_Consultant where Con_ID = " & intSignatureConID)
            EmailFrom = DB.GetSQLScalar(sqlbldr.ToString)
            If intSignatureConID <> "" Then
                Signature = CommonDataFunctions.GblFunc_Get_Sigfile(intSignatureConID)
            End If
            OpToEmail = OpToEmail & vbCrLf & Signature
            'EMAILBCCSTRING
            EMAILBODYSTRING = OpToEmail
            EMAILSUBJECTSTRING = GlobalCRM.subj
            EMAILFROMSTRING = EmailFrom
            EMAILTOSTRING = EmailTo
            'frmEmailCaseNavigation.btnBack.Text = "Cancel"
            'frmEmailCaseNavigation.ShowDialog()
            
            Dim Success As Integer = Letters.GblFunc_Send_MailEngine(EMAILTOSTRING, EMAILFROMSTRING, EMAILSUBJECTSTRING, EMAILBODYSTRING, , EMAILBCCSTRING)
            EMAILSUBJECTSTRING = String.Empty
            EMAILBODYSTRING = String.Empty
            EMAILBCCSTRING = String.Empty
            Dim Msg As New StringBuilder
            With Msg
                .Append("Email Sent" & vbCrLf & vbCrLf & "To: " & EmailTo)

                .Append(vbCrLf & vbCrLf & "Sent via mail engine")
                .Append(vbCrLf & "Subject: " & GlobalCRM.subj)

                .Append(vbCrLf & "Message: " & vbCrLf & vbCrLf & OpToEmail)
            End With

            tsk.GblFunc_Add_Event("Send Email", Msg.ToString, TasksEvents.EventStatus.SingleEvent, GlobalCRM.CurrentCaseID, TasksEvents.EventTypes.EventCase, , , , , 1)
            If Success = 1 Then
                'MsgBox("Email has been sent to the client successfully.", MsgBoxStyle.Information, "Success")
            End If
            System.IO.File.Delete(txtfile)
            cmn.ForceDeleteTempDir()

        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "commonFunc.SendEmailForEditCase", True)
            db = Nothing
            tsk = Nothing
            cmn = Nothing
            Exit Sub
        End Try
    End Sub
    Public Shared Function GetSkillOccupationCategory(ByVal OccupationId As Integer) As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("select skilledocc_categoryid from l_skilledocc where skilledocc_id = " & OccupationId)
        Catch ex As Exception
        Finally
            db = Nothing

        End Try
    End Function
    Public Shared Function GblFunc_Validate_NatResVisa(ByVal NatID As Integer, ByVal ResID As Integer, ByVal UKVisaID As Integer, ByVal ExpDate As Integer, ByVal Echo As Integer) As Integer
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As New StringBuilder

        Dim CountryEU As Integer
        Dim myScal As Object
        Dim UKVisaILR As Object
        Dim strProductType As String
        Try


            strProductType = CommonDataFunctions.GetCaseProductType(GlobalCRM.CurrentCaseID)

            If strProductType <> "GBR-GBR" Then
                Return 0
            End If

            GblFunc_Validate_NatResVisa = 10

            'Is the provided Nationality in the EU?
            SQL.Append("Select Country_EU from L_Country where country_ID = " & NatID)
            myScal = db.GetSQLScalar(SQL.ToString)

            If IsNothing(myScal) Then
                Return 10
                Exit Function
            Else
                CountryEU = myScal
            End If

            'Validate exp date vs visa type
            If ExpDate = 0 And UKVisaID <> 1 And UKVisaID <> 36 Then '1 = no uk visa (british) 36 =unknown
                'No exp date and UK visa provided, check if exp date needed

                SQL = New StringBuilder
                SQL.Append("SELECT UKVisa_Indefinite FROM L_UKVisa  WHERE UKVisa_ID = " & UKVisaID)

                UKVisaILR = db.GetSQLScalar(SQL.ToString)

                If IsDBNull(UKVisaILR) Then
                    'Error state, couldn't find UKVisa type
                    GblFunc_Validate_NatResVisa = 10
                    Exit Function
                End If

                If UKVisaILR = 0 Then

                    'This Visa type requires an expiry date

                    If Echo = 1 Then
                        'Prompt user to check if ok
                        If MsgBox("This UK visa type requires an expiry date, but you have not provided one. Are you sure you wish to proceed?", vbYesNo + vbQuestion, "Error") = MsgBoxResult.Yes Then
                            GblFunc_Validate_NatResVisa = 4
                        Else
                            GblFunc_Validate_NatResVisa = 3
                        End If
                    Else
                        GblFunc_Validate_NatResVisa = 4
                    End If

                    Exit Function
                End If

            End If

            'Check for Unknown values
            If NatID = 84 Or ResID = 84 Or UKVisaID = 36 Then
                'Unknown values present, cannot validate
                GblFunc_Validate_NatResVisa = 5
            Else
                If CountryEU = 1 Then
                    'Nationality is in EU, so no UKVisa required and Residence irrelevent

                    'Test UK visa set
                    If UKVisaID <> 1 Then
                        'EU national with UK visa set, confirm they mean to do this
                        If Echo = 1 Then
                            If MsgBox("You have selected a visa status for someone with EU Nationality. Are you sure this is correct?", vbYesNo, "Confirmation") <> 6 Then
                                GblFunc_Validate_NatResVisa = 3
                            Else
                                GblFunc_Validate_NatResVisa = 0
                            End If
                        Else
                            GblFunc_Validate_NatResVisa = 0
                        End If
                    Else
                        GblFunc_Validate_NatResVisa = 0
                    End If
                Else
                    'Non-EU national, verrify other fields
                    If ResID = GlobalCRM.COUNTRY_UK Then
                        'Non EU Nat, resident in the UK, needs UK Visa

                        If UKVisaID = 1 Then
                            If Echo = 1 Then MsgBox("You must select a current visa status for non-EU nationals resident in country", MsgBoxStyle.Exclamation, "Error")
                            GblFunc_Validate_NatResVisa = 3
                        Else
                            'Non EU Nat, resident here with a visa provided (exp date checked above), this is ok
                            GblFunc_Validate_NatResVisa = 0
                        End If
                    Else
                        'Non EU Nat, not resident here, UK Visa type irrelevent
                        GblFunc_Validate_NatResVisa = 0
                    End If
                End If
            End If

            Exit Function
        Catch ex As Exception
            GblFunc_Validate_NatResVisa = 0
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function SkilledOccupationCategories(ByVal CountryId As Integer) As DataTable
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("SELECT SkilledCategory_ID, SkilledCategory_Name FROM L_SkilledCategories")
                .Append(" WHERE (SkilledCategory_CountryID = " & CountryId & ")")
                .Append(" ORDER BY SkilledCategory_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function SkilledOccupationList(ByVal CategoryId As Integer) As DataTable

        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL
                .Append("Select SkilledOcc_ID, SkilledOcc_Title from L_SkilledOcc ")
                .Append(" Where SkilledOcc_CategoryID = " & CategoryId)
                .Append(" AND SkilledOcc_Inactive = 0")
                .Append(" Order By SkilledOcc_Title")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try

    End Function
    Public Shared Function GetConEmailFromID(ByVal Conid As Integer) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("SELECT Con_Email FROM dbo.L_Consultant WHERE Con_ID = " & Conid)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function LatestVersionRolledOut() As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Dim strVersion As String
            strVersion = db.GetSQLScalar("select max(version_number) from d_Version_CRM3").ToString
            Return strVersion.Trim
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetBranchInstallationPath() As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Try
            SQL = New StringBuilder
            With SQL


                .Append(" Select dbo.L_Branch.Branch_InstallationPath")
                .Append(" FROM         dbo.L_Consultant INNER JOIN")
                .Append(" dbo.L_Branch ON dbo.L_Consultant.Con_BranchID = dbo.L_Branch.Branch_ID")
                .Append(" WHERE     dbo.L_Consultant.Con_ID = " & GlobalCRM.CurrentConID)
            End With
            Return db.GetSQLScalar(SQL.ToString)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function


    Public Shared Function getDraftForConsultant(ByVal conid As Long) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try
            Return db.GetSQLScalar("select con_EmaildraftLoc from l_consultant where con_id = " & conid)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing

        End Try
    End Function


    Public Shared Function CheckWhethetherTask(ByVal eventid As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            If db.GetSQLScalar("Select event_category from d_event where event_id = " & eventid) = 2 Then
                Return True
            Else : Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try

    End Function
    Public Shared Function GetCustomizationValue(ByVal TextColumn As String) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("Select customize_Data from l_customization where customize_text like '" & TextColumn & "'")

        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try

    End Function
    Public Shared Function HaveJobOffer(ByVal caseid As Long) As Long
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar("Select case_employmentoffer from d_case where case_id = " & caseid)
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try

    End Function
    Public Shared Function GetPipeline(ByVal ConId As Integer) As String
        ' get Pipeline message displayed at bottom of frmMainForm
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim SQL As StringBuilder
        Dim myData As DataTable
        Dim Msg As StringBuilder
        Dim myRow As DataRow
        Try

        
            SQL = New StringBuilder
            Msg = New StringBuilder
            ' get number of active leads and leads on hold
            With SQL
                .Append("Select Count(Case_ID) as Leads, coalesce(Sum (Case_activeStatus),0) as LeadOnHold")
                .Append(" From D_Case")
                .Append(" Where Case_StatusId < 4")
                .Append(" And Case_ConSaleId = " & ConId)
            End With

            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then

                    myRow = myData.Rows(0)

                    With Msg

                        .Append(myRow("Leads") & " Leads ")
                        If myRow("leadonhold") > 0 Then
                            .Append("(" & myRow("leadonhold") & " on Hold)")
                        End If

                        .Append(" : ")

                    End With

                End If
            End If

            ' get number of sold and open cases
            SQL = New StringBuilder

            With SQL
                .Append("Select Count(Case_ID) as Cases, coalesce(Sum (Case_activeStatus),0) as CaseOnHold")
                .Append(" From D_Case")
                .Append(" WHERE (D_Case.Case_StatusID BETWEEN 4 AND 10) and (D_Case.Case_StatusID NOT IN(17,18))")
                .Append(" And Case_ConCWID = " & ConId)
            End With


            myData = db.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    myRow = myData.Rows(0)

                    With Msg

                        .Append(myRow("cases") & " Initiated cases ")
                        If myRow("caseonhold") > 0 Then
                            .Append("(" & myRow("caseonhold") & " on Hold)")
                        End If

                    End With

                End If
            End If

            ' get Web Loop Score for selected consultant (uses SP_ConPoints stored procedure)
            SQL = New StringBuilder

            With SQL
                .Append("Declare @Pts integer" & vbCrLf)
                .Append("Declare @Nothing integer" & vbCrLf & vbCrLf)
                .Append("Exec SP_ConPoints @ConID = " & ConId & ", @Points = @Pts Output")
                .Append(" , @OverdueTasks = @Nothing  Output, @NoTasks = @Nothing  Output")
                .Append(" , @DaysSinceTask = @Nothing  Output, @NewWebLeads = @Nothing  Output")
                .Append(" , @NewExpLeads = @Nothing  Output, @NewProspect = @Nothing  Output")
                .Append(" , @Benchmark = @Nothing  Output" & vbCrLf & vbCrLf)
                .Append("Select @Pts as ConPoints")
            End With

            myData = Nothing
            'myData = DB.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then
                If myData.Rows.Count > 0 Then
                    myRow = myData.Rows(0)

                    Msg.Append("Web Loop Score: " & myRow("ConPoints"))
                Else
                    Msg.Append("Web Loop Score: N/A")
                End If
            End If

            If Not myData Is Nothing Then

                myData.Dispose()
            End If
            Return Msg.ToString
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
            SQL = Nothing
            myData = Nothing
            Msg = Nothing
            myRow = Nothing
        End Try


    End Function
    Public Shared Function getCustomizationdata(ByVal customizadata As String) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try

            Return db.GetSQLScalar("select customize_data from l_customization where customize_text like '" & customizadata.Trim & "'")
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function UpdateUserNTPassword(ByVal username As String, ByVal password As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim dt As DataTable
        Try
            dt = db.GetSQLDataTable("select * from l_consultant where con_name like '" & username & "'")
            If dt.Rows.Count = 1 Then
                Return db.ExecuteSQL("Update l_consultant set con_Password =  '" & password & "' where con_name = '" & username & "'")

            Else
                Return False
            End If
        Catch ex As Exception
            Return True
        Finally
            db = Nothing
            dt = Nothing
        End Try
    End Function
    Public Shared Function UpdateUserEmailPassword(ByVal username As String, ByVal password As String) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim dt As DataTable
        Try
            dt = db.GetSQLDataTable("select * from l_consultant where con_name like '" & username & "'")
            If dt.Rows.Count = 1 Then
                Return db.ExecuteSQL("Update l_consultant set con_emailpassword =  '" & password & "' where con_name = '" & username & "'")

            Else
                Return False
            End If
        Catch ex As Exception
            Return True
        Finally
            db = Nothing
            dt = Nothing
        End Try
    End Function

    Public Shared Function GetUserNTPassword(ByVal username As String) As String
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Dim dt As DataTable
        Try
            dt = db.GetSQLDataTable("select * from l_consultant where con_name like '" & username & "'")
            If dt.Rows.Count = 1 Then

                Return db.GetSQLScalar("select con_Password from l_consultant where con_name = '" & username & "'")
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        Finally
            db = Nothing
            dt = Nothing
        End Try
    End Function
    Public Shared Function UpdateGVScore(ByVal caseid As Long, ByVal value As Integer) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.ExecuteSQL("Update d_case set case_gvscore = " & value & " where case_id = " & caseid)

        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function Usersetting(ByVal settingid As Long, ByVal conid As Long) As Long
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Try
            Return db.GetSQLScalar(" Select settings_value FROM         dbo.l_settings WHERE     (settings_linkid = " & settingid & ") AND (settings_conid = " & conid & ")")
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function UpdateUsersetting(ByVal settingid As Long, ByVal conid As Long, ByVal value As Long) As Boolean
        Dim db As New Global_Visas_Data_Layer.DataAccess
        Dim dt As DataTable
        Try
            dt = db.GetSQLDataTable(" Select settings_linkid,settings_value ,settings_conid FROM         dbo.l_settings WHERE     (settings_linkid = " & settingid & ") AND (settings_conid = " & conid & ")")
            If Not dt Is Nothing Then
                If dt.Rows.Count > 0 Then
                    Return db.ExecuteSQL("update l_settings set settings_value= " & value & " where settings_conid = " & conid & " and settings_linkid =" & settingid)
                End If
                If dt.Rows.Count = 0 Then
                    Return db.ExecuteSQL(" insert into l_settings (settings_conid,settings_linkid,settings_value) values(" & conid & "," & settingid & "," & value & ") ")
                End If
            Else
                Return db.ExecuteSQL(" insert into l_settings (settings_conid,settings_linkid,settings_value) values(" & conid & "," & settingid & "," & value & ") ")
            End If
        Catch ex As Exception
            Return False
        Finally
            db = Nothing
            dt = Nothing
        End Try
    End Function
    Public Shared Function GetLocalServer() As DateTime
        Dim db As New Global_Visas_Data_Layer.DataAccess

        Try

            Return db.GetSQLScalar("select getdate()")

        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function
End Class





